Retrieve data from column as a row - Using LISTAGG function - Oracle

Retrieve data from column as a row.


Lets use employee table from Scott as a an example :

select * from scott.employee;

EMPLOYEE_ID FIRST_NAME    LAST_NAME      DEPT_NO     SALARY
----------- -------------------- -------------------- ---------- ----------
          1 Dan                  Morgan                       10     100000
          2 Helen                Lofstrom                     20     100000
          3 Akiko                Toyota                       20      50000
          4 Jackie               Stough                       20      40000
          5 Richard              Foote                        20      70000
          6 Joe                  Johnson                      20      30000
          7 Clark                Urling                       20      90000
          9 Richard              Foote                        20      70001
          8 Clark                Urling                       20      90001


Now I want to get the list of all users in a particular department. I want the output to be printed something like this :

I can achieve this by using the simple LISTAGG function provided by Oracle to get this results.In this below example I got ll the users ID,FNAME,LNAME in every dept.

DEPT_NO   EMP_DETAILS
 
        10   1:Dan-Morgan                                                                        
        20   2:Helen-Lofstrom,3:Akiko-Toyota,4:Jackie-Stough,5:Richard-Foote,6:Joe-                                                      Johnson,7:Clark-Urling,8:Clark-Urling,9:Richard-Foote
       


Code :

select DEPT_NO,LISTAGG(EMPLOYEE_ID ||':' ||FIRST_NAME||'-'||last_name,',')  WITHIN GROUP (order by EMPLOYEE_ID,first_name) "EMP DETAILS" from scott.employee group by dept_no;

usage - we can call listagg function and need to pass the columns that you want to retrieve data from and transform them into rows.

Listagg (column1||'-'||column2) make sure you pass a common column like the deptno thru which you want to group the data.