How to Show All employee names within a department in SQL(Listagg)

View All Scripts Login to Run Script

Script Name How to Show All employee names within a department in SQL(Listagg)
Description How to Show All employee names within a department in SQL
Area SQL General / SQL Query
Contributor Evaluation
Created Monday January 25, 2016

Statement 1

  SELECT (SELECT department_name
            FROM hr.departments
           WHERE department_id = e.department_id)
            AS "Department",
         LISTAGG (last_name, ', ') WITHIN GROUP (ORDER BY department_id)
            "Employees"
    FROM hr.employees e
GROUP BY department_id
ORDER BY 1

 
Department Employees
Accounting Gietz, Higgins
Administration Whalen
Executive De Haan, King, Kochhar
Finance Chen, Faviet, Greenberg, Popp, Sciarra, Urman
Human Resources Mavris
IT Austin, Ernst, Hunold, Lorentz, Pataballa
Marketing Fay, Hartstein
Public Relations Baer
Purchasing Baida, Colmenares, Himuro, Khoo, Raphaely, Tobias
Sales Abel, Ande, Banda, Bates, Bernstein, Bloom, Cambrault, Cambrault, Doran, Errazuriz, Fox, Greene, Hall, Hutton, Johnson, King, Kumar, Lee, Livingston, Marvins, McEwen, Olsen, Ozer, Partners, Russell, Sewall, Smith, Smith, Sully, Taylor, Tucker, Tuvault, Vishney, Zlotkey
Shipping Atkinson, Bell, Bissot, Bull, Cabrio, Chung, Davies, Dellinger, Dilly, Everett, Feeney, Fleaur, Fripp, Gates, Gee, Geoni, Grant, Jones, Kaufling, Ladwig, Landry, Mallin, Markle, Marlow, Matos, McCain, Mikkilineni, Mourgos, Nayer, OConnell, Olson, Patel, Perkins, Philtanker, Rajs, Rogers, Sarchand, Seo, Stiles, Sullivan, Taylor, Vargas, Vollman, Walsh, Weiss
 - Grant

12 rows selected.

Department	Employees
Accounting	Gietz, Higgins
Administration	Whalen
Executive	De Haan, King, Kochhar
Finance	Chen, Faviet, Greenberg, Popp, Sciarra, Urman
Human Resources	Mavris
IT	Austin, Ernst, Hunold, Lorentz, Pataballa
Marketing	Fay, Hartstein
Public Relations	Baer
Purchasing	Baida, Colmenares, Himuro, Khoo, Raphaely, Tobias
Sales	Abel, Ande, Banda, Bates, Bernstein, Bloom, Cambrault, Cambrault, Doran, Errazuriz, Fox, Greene, Hall, Hutton, Johnson, King, Kumar, Lee, Livingston, Marvins, McEwen, Olsen, Ozer, Partners, Russell, Sewall, Smith, Smith, Sully, Taylor, Tucker, Tuvault, Vishney, Zlotkey
Shipping	Atkinson, Bell, Bissot, Bull, Cabrio, Chung, Davies, Dellinger, Dilly, Everett, Feeney, Fleaur, Fripp, Gates, Gee, Geoni, Grant, Jones, Kaufling, Ladwig, Landry, Mallin, Markle, Marlow, Matos, McCain, Mikkilineni, Mourgos, Nayer, OConnell, Olson, Patel, Perkins, Philtanker, Rajs, Rogers, Sarchand, Seo, Stiles, Sullivan, Taylor, Vargas, Vollman, Walsh, Weiss
-	Grant

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database