SELECT 1 EMPID, 'a' NAME, '1,2' DEPTID

FROM DUAL

UNION ALL

SELECT 2 EMPID, 'b' NAME, '' DEPTID

FROM DUAL

UNION ALL

SELECT 3 EMPID, 'c' NAME, '1,2,3' DEPTID

FROM DUAL;

AS

SELECT 1 DEPTID, 'IT' DEPTNAME

FROM DUAL

UNION ALL

SELECT 2 DEPTID, 'Finance' DEPTNAME

FROM DUAL

UNION ALL

SELECT 3 DEPTID, 'HR' DEPTNAME

FROM DUAL;

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;