WITH member AS ( SELECT '0000003841' memberid, '홍길동' name, '10' deptid FROM dual UNION ALL SELECT '0000009358', '김유신', '20' FROM dual ) , dept AS ( SELECT '10' deptid, '총무부' deptname FROM dual UNION ALL SELECT '20', '기획부' FROM dual ) , data_t AS ( SELECT '0000003841/0000009358' memberid FROM dual ) SELECT a.memberid , SUBSTR(XMLAGG(XMLELEMENT(x, '/', b.name) ORDER BY INSTR(a.memberid, b.memberid)).EXTRACT('//text()'), 2) name , SUBSTR(XMLAGG(XMLELEMENT(x, '/', c.deptname) ORDER BY INSTR(a.memberid, b.memberid)).EXTRACT('//text()'), 2) deptname FROM data_t a , member b , dept c WHERE INSTR(a.memberid, b.memberid) > 0 AND b.deptid = c.deptid GROUP BY a.memberid ;