Returning comma separated list

 To return a comma separated list of employees in each department we use the LISTAGG Analytical Function as below.

SELECT department_id, LISTAGG(First_Name,',') WITHIN GROUP (ORDER BY First_Name)
FROM   employees
GROUP BY department_id
ORDER BY department_id;

Department Id	Employees
10	Jennifer
20	Michael,Pat
30	Alexander,Den,Guy,Karen,Shelli,Sigal
40	Susan
50	Adam,Alana,Alexis,Anthony,Britney,Curtis,Donald,Douglas, Girard,Hazel,Irene,James,James,Jason,Jean,Jennifer,John, Joshua,Julia,Julia,Kelly,Kevin,Kevin,Ki,Laura,Martha, Matthew,Michael,Mozhe,Nandita,Payam,Peter,Randall,Randall, Renske,Samuel,Sarah,Shanta,Stephen,Steven,TJ, Timothy,Trenna,Vance,Winston
60	Alexander,Bruce,David,Diana,Valli
70	Hermann
80	Alberto,Allan,Alyssa,Amit,Charles,Christopher,Clara, Danielle,David,David,Eleni,Elizabeth,Ellen,Gerald,Harrison, Jack,Janette,John,Jonathon,Karen,Lindsey,Lisa,Louise,Mattea, Nanette,Oliver,Patrick,Peter,Peter,Sarath,Sundar,Sundita,Tayler, William
90	Lex,Neena,Steven
100	Daniel,Ismael,John,Jose Manuel,Luis,Nancy
110	Shelley,William
	Kimberely

In the results we see some duplicates, if we want to remove the duplicates then we use the distinct keyword as below.

SELECT department_id

              , LISTAGG( DISTINCT First_Name,',') WITHIN GROUP (ORDER BY First_Name)
FROM   employees
GROUP BY department_id
ORDER BY department_id;

Department Id	Employees
10	Jennifer
20	Michael,Pat
30	Alexander,Den,Guy,Karen,Shelli,Sigal
40	Susan
50	Adam,Alana,Alexis,Anthony,Britney,Curtis,Donald, Douglas,Girard,Hazel,Irene,James,Jason,Jean,Jennifer, John,Joshua,Julia,Kelly,Kevin,Ki,Laura, Martha,Matthew,Michael,Mozhe,Nandita, Payam,Peter,Randall,Renske, Samuel,Sarah,Shanta,Stephen,Steven,TJ,Timothy, Trenna,Vance,Winston
60	Alexander,Bruce,David,Diana,Valli
70	Hermann
80	Alberto,Allan,Alyssa,Amit,Charles,Christopher, Clara,Danielle,David,Eleni, Elizabeth,Ellen,Gerald,Harrison,Jack,Janette, John,Jonathon,Karen,Lindsey, Lisa,Louise,Mattea,Nanette,Oliver,Patrick,Peter, Sarath,Sundar,Sundita,Tayler, William
90	Lex,Neena,Steven
100	Daniel,Ismael,John,Jose Manuel,Luis,Nancy
110	Shelley,William
	Kimberely

  

There is another way to do this in Oracle 21c

SELECT department_name, 

            CURSOR(SELECT salary, commission_pct 

            FROM employees e

            WHERE e.department_id = d.department_id)

FROM departments d

ORDER BY department_name

How to find the 2nd lowest salary in each department

SELECT 

    department_id, first_name, salary, 

    NTH_VALUE(salary, 2) OVER (PARTITION BY department_id 

ORDER BY salary 

RANGE BETWEEN UNBOUNDED PRECEDING 

AND UNBOUNDED FOLLOWING) "2nd_lowest_sal"

FROM employees

ORDER BY department_id, salary;