Formatting Oracle query output

Question

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!

Tim Biegeleisen · Accepted Answer · 2019-01-22 15:19:25Z

1

Try using LISTAGG:

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

Demo

answered Jan 22, 2019 at 15:19

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Aarie Over a year ago

@Tim..its working fine but the TASKNAME column values are coming in scattered letters, i.e. P r o c e s s m a n a g e r, D B A u s e r, F l o w c o o r d i n a t o r.

Tim Biegeleisen Over a year ago

@Aarie This sounds like a presentation problem, not really an issue with my query. Check your encoding for starters.

Aarie Over a year ago

@Tim..I have simply put it like this

WITH MyTable AS ( 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)

. Not sure from where its picking a trigger for this scattering and that too only in last column.

Tim Biegeleisen Over a year ago

This looks like an encoding problem to me actually. Or maybe some issue with your configuration of Oracle SQL developer. My code works in the demo without this problem.

Aarie Over a year ago

It looks like something else, because its coming like this even using script connecting to database and spooling the output. Anyway Thanks for your help ! :-)

|

Collectives™ on Stack Overflow

Formatting Oracle query output

1 Answer 1

Demo

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related