get names from comma separated ids in SQL

Question

I have this kind of table in Oracle

empid name deptid
1      a    1,2
2      b
3      c    1,2,3

and I have department table as below,

Deptid DeptName
 1      IT
 2      Finance
 3      HR

i want result like this in select statement,

empid name dept
1     a    IT,Finance
2     b
3     c    IT,Finance,HR

i have 1000+ rows in both tables, i am beginner to pl\sql and don't know how to do this, please help.

You shouldn't be storing comma separated values to begin with. Read up on [database normalization](https://en.wikipedia.org/wiki/Database_normalization) — , Aug 20 '18 at 12:12
Possible duplicate of [converting comma separated value to multiple rows](https://stackoverflow.com/questions/51928702/converting-comma-separated-value-to-multiple-rows) — , Aug 20 '18 at 12:12

score 0 · Accepted Answer · answered Aug 20 '18 at 12:56

To do that join a table with integers, so that every employee row occurs as often as there are department IDs in its string but at least one time. For the rows in the join result the numbers i go from 1 to n, where n is the number of IDs in the string for that employee (if there are any department IDs for the employee). Then you can use REGEXP_SUBSTR() to get the _i_th number from the string. Use that to left join the departments, to get the department name. Then use an aggregation using LISTAGG() to get a single row for each employee again.

SELECT E.EMPID,
       E.NAME,
       E.DEPTID,
       LISTAGG(D.DEPTNAME, ',') WITHIN GROUP (ORDER BY I.I) DEPTNAME
       FROM EMPLOYEE E
            LEFT JOIN (SELECT ROW_NUMBER() OVER (ORDER BY DEPTID) I
                              FROM DEPARTMENT) I
                      ON I.I <= REGEXP_COUNT(E.DEPTID, ',') + 1
            LEFT JOIN DEPARTMENT D
                      ON D.DEPTID = TO_NUMBER(REPLACE(REGEXP_SUBSTR(',' || E.DEPTID, ',([[:digit:]]+)', 1, I.I), ',', ''))
       GROUP BY E.EMPID,
                E.NAME,
                E.DEPTID;

db<>fiddle

get names from comma separated ids in SQL

1 Answers1