How to convert Multiple row values to one row with comma-separated value different ID in Oracle

Question

I am trying to return data in comma separated values with below query.

select u.employeeid,
       ( select name
         from   roles r
         where  r.id = ur.role_id
       ) userrole
from   users u,
       user_role ur
where  u.id in (select DISTINCT ur.user_id
                from user_role ur)
order by u.employeeid asc;

Current Output:

EMPLOYEEID USERRROLE
---------------------
1000    ROLE_SUPER_ADMIN
1000    ONBOARDING_CHECKER
1000    ROLE_SUPER_ADMIN
1000    ROLE_APPROVER
1000    ROLE_ONBORDING

Now when I execute the query, I want table to be like:

EMPLOYEEID USERRROLE
--------------------
1000       ROLE_SUPER_ADMIN,ONBOARDING_CHECKER,ROLE_SUPER_ADMIN,ROLE_APPROVER,ROLE_ONBORDING

Answer 1

Use LISTAGG (and ANSI joins rather than legacy comma joins and confusing sub-queries). What you probably want is:

SELECT u.employeeid,
       LISTAGG( r.name, ',' ) WITHIN GROUP ( ORDER BY r.name ) AS userrole
FROM   users u
       INNER JOIN user_role ur
       ON ( u.id = ur.user_id )
       LEFT OUTER JOIN roles r
       ON ( r.id = ur.role_id )
GROUP BY u.employeeid
ORDER BY u.employeeid asc;

Although I think what you have actually written would be:

SELECT u.employeeid,
       LISTAGG( r.name, ',' ) WITHIN GROUP ( ORDER BY r.name ) AS userrole
FROM   users u
       CROSS JOIN user_role ur
       LEFT OUTER JOIN roles r
       ON ( r.id = ur.role_id )
WHERE  EXISTS ( SELECT 1
                FROM   user_roles x
                WHERE  u.id = x.user_id )
GROUP BY u.employeeid
ORDER BY u.employeeid asc;

Answer 2

this will do:

SELECT LISTAGG("USERRROLE", ',') WITHIN GROUP (ORDER BY "USERRROLE")
FROM Table1 group by "EMPLOYEEID";

check fiddle:http://sqlfiddle.com/#!4/d11c6/4

Thanks!!!!!!!