Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE EMPLOYEE
AS
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;
3 rows affected

CREATE TABLE DEPARTMENT
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;
3 rows affected

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;


EMPID
NAME
DEPTID
DEPTNAME


1
a
1,2
IT,Finance

2
b
null
null

3
c
1,2,3
IT,Finance,HR