WITH T (SEQ , TXT) AS ( SELECT 1 , '1,3,11' TXT FROM DUAL ) , T_MAP ( CD , NM ) AS ( SELECT '1' , '사과' FROM DUAL UNION ALL SELECT '2' , '배' FROM DUAL UNION ALL SELECT '3' , '감' FROM DUAL UNION ALL SELECT '4' , '귤' FROM DUAL UNION ALL SELECT '11' , '오렌지' FROM DUAL UNION ALL SELECT '12' , '바나나' FROM DUAL UNION ALL SELECT '13' , '파인애플' FROM DUAL UNION ALL SELECT '14' , '자몽' FROM DUAL ) SELECT LISTAGG(B.NM,',') WITHIN GROUP (ORDER BY RN ) FROM (SELECT LV RN , REGEXP_SUBSTR(TXT,'[^,]+',1,LV) CD FROM T AA , (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 15 ) BB WHERE REGEXP_COUNT(TXT,',') + 1 >= LV ) A , T_MAP B WHERE A.CD = B.CD
WITH t AS ( SELECT 1 seq, '1,3,11' txt FROM dual UNION ALL SELECT 2, '3,1,11' FROM dual ) , t_map AS ( SELECT '1' cd, '사과' nm FROM dual UNION ALL SELECT '2', '배' FROM dual UNION ALL SELECT '3', '감' FROM dual UNION ALL SELECT '4', '귤' FROM dual UNION ALL SELECT '11', '오렌지' FROM dual UNION ALL SELECT '12', '바나나' FROM dual UNION ALL SELECT '13', '파인애플' FROM dual UNION ALL SELECT '14', '자몽' FROM dual ) SELECT a.seq, a.txt , LISTAGG(b.nm, ',') WITHIN GROUP(ORDER BY INSTR(','||a.txt||',', ','||b.cd||',')) nm FROM t a , t_map b WHERE INSTR(','||a.txt||',', ','||b.cd||',') > 0 GROUP BY a.seq, a.txt ;