WITH T AS ( SELECT 'ABC,BCD,CDE' VAL, 1 SEQ FROM DUAL UNION ALL SELECT 'DEF,EFG,FGH' VAL, 2 SEQ FROM DUAL ) , T1 AS (SELECT 'ABC' CODE, '기타1' NM FROM DUAL UNION ALL SELECT 'BCD' CODE, '기타2' NM FROM DUAL UNION ALL SELECT 'CDE' CODE, '기타3' NM FROM DUAL UNION ALL SELECT 'DEF' CODE, '기타4' NM FROM DUAL UNION ALL SELECT 'EFG' CODE, '기타5' NM FROM DUAL UNION ALL SELECT 'FGH' CODE, '기타6' NM FROM DUAL ) SELECT SEQ, LISTAGG(T1.NM,',') WITHIN GROUP(ORDER BY LV) NM FROM ( SELECT REGEXP_SUBSTR(VAL,'[^,]+',1,LV) CODE, LV, SEQ FROM T, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL < 20)-- 콤마 구분자가 올 수 있는 최대값 WHERE LV <= REGEXP_COUNT(VAL,',') + 1 ) A, T1 WHERE A.CODE = T1.CODE GROUP BY SEQ
원하시는 게 맞는지 모르겠네요.