아래처럼 변형해서 처리하시려는 걸까요..?
1 2 3 4 5 6 7 8 9 | SELECT REGEXP_SUBSTR( 'code1|code2|code3' , '[^|]+' , 1, level ) AS parts FROM dual CONNECT BY REGEXP_SUBSTR( 'code1|code2|code3' , '[^|]+' , 1, level ) IS NOT NULL ; SELECT REGEXP_SUBSTR( '코드한글명1,코드한글명2,코드한글명3' , '[^,]+' , 1, level ) AS parts FROM dual CONNECT BY REGEXP_SUBSTR( '코드한글명1,코드한글명2,코드한글명3' , '[^,]+' , 1, level ) IS NOT NULL ; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT 'CODE1' AS COL1 , A2. NAME FROM DUAL A1 , CODENAME A2 WHERE A1.COL = A2.CODE 위처럼 단일건이라면 A2와 조인을 통해 데이터를 받을수 있지만, 데이터가 N건이상이라면 아래와같이 |구분자를통해 넣어주고있습니다.. SELECT 'CODE1|CODE2' AS COL1 , A2. NAME FROM DUAL A1 , CODENAME A2 WHERE A1.COL = A2.CODE 이런식인데.. 어떻게 조인을 해야할지 도무지 감이 안잡힙니다 ㅜ |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | WITH t_code AS ( SELECT 'CODE1' cd, '코드한글명1' nm FROM dual UNION ALL SELECT 'CODE2' , '코드한글명2' FROM dual UNION ALL SELECT 'CODE3' , '코드한글명3' FROM dual ) , t_data AS ( SELECT 1 pk, 'CODE1|CODE2|CODE3' cd FROM dual UNION ALL SELECT 2, 'CODE1|CODE3' FROM dual UNION ALL SELECT 3, 'CODE3|CODE1' FROM dual ) SELECT a.pk , a.cd , LISTAGG(b.nm, ',' ) WITHIN GROUP ( ORDER BY INSTR( '|' ||a.cd|| '|' , '|' ||b.cd|| '|' )) nm FROM t_data a , t_code b WHERE INSTR( '|' ||a.cd|| '|' , '|' ||b.cd|| '|' ) > 0 GROUP BY a.pk, a.cd ; |