CODEWITHSUNDEEP
sqloracle-database
Aarie
Aarie

Reputation: 31

Formatting Oracle query output

I am running following query against a schema in Oracle database to fetch some details....

SELECT USERNAME, FULLNAME, DEPARTMENT, TASKNAME 
  FROM USERDB t1 
  JOIN USERDB_TASKS t3 ON t1.USERID=t3.USERID 
  JOIN TASKS t2 ON t3.TASKSID=t2.TASKSID;

Here table USERDB contains column names USERID, USERNAME, FULLNAME, DEPARTMENT. Table USERDB_TASKS contains column names USERID, TASKSID. Table TASKS contains column names TASKSID, TASKNAME. For a particular user, USERID will be same in all tables. Similarly TASKSID for a particular task will be same in all tables.

I am getting output in below format:

USERNAME FULLNAME     DEPARTMENT TASKNAME
duryo    dur yogeli   IT         Domain admin
duryo    dur yogeli   IT         Session user
rected   rec tedenson SALES      Process manager
rected   rec tedenson SALES      DBA user
rected   rec tedenson SALES      Flow coordinator 
................................................

I mean for the same user there are multiple tasknames coming in last column.

The output format I want to create is like below...

USERNAME FULLNAME     DEPARTMENT TASKNAME
duryo    dur yogeli   IT         Domain admin, Session user
rected   rec tedenson SALES      Process manager, DBA user, Flow coordinator

Although I have a batch script which does this formatting for me after generating the output in a file, but I want to generate the formatted output using oracle query itself, if possible.

Any clue would be appreciated. Thank you!

Upvotes: 0

Views: 283

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

Try using LISTAGG:

SELECT
    USERNAME,
    FULLNAME,
    DEPARTMENT,
    LISTAGG(TASKNAME, ', ') WITHIN GROUP (ORDER BY TASKNAME) TASKNAME
FROM yourTable
GROUP BY
    USERNAME,
    FULLNAME,
    DEPARTMENT;

enter image description here

Demo

Upvotes: 1

Related Questions