안녕하세요
고민되는 SQL이 있어서 고수님들께 질문을 드려야 할것 같아 올리게 되었습니다.
- 질문 -
다음의 매핑 테이블이 있습니다.
asis | tobe |
홍길동 | A1 |
길말자 | B7 |
김치 | C4 |
감자 | K3 |
테이블의 데이터는 아래와 같습니다.
홍길동,김치 |
김치,감자 |
감자 |
홍길동,길말자,김치 |
1. 테이블의 데이터는 사용자명 을 ',' 구분자로 여러건이 한 로우에 들어가 있음
2. '사용자명' --> '사용자ID' 로 매핑 테이블에 정의 된 TOBE 데이터로 변경하고 싶습니다.
3. 변경후 데이터
A1,C4 |
C4,K3 |
K3 |
A1,B7,C4 |
감사합니다.^^
WITH T1(ASIS, TOBE) AS ( SELECT '홍길동', 'A1' FROM DUAL UNION ALL SELECT '길말자', 'B7' FROM DUAL UNION ALL SELECT '김치' , 'C4' FROM DUAL UNION ALL SELECT '감자' , 'K3' FROM DUAL ), T2(TXT) AS ( SELECT '홍길동,김치' FROM DUAL UNION ALL SELECT '김치,감자' FROM DUAL UNION ALL SELECT '감자' FROM DUAL UNION ALL SELECT '홍길동,길말자,김치' FROM DUAL ), T3(ROW_NUM) AS ( SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(REGEXP_COUNT(TXT, '[^,]+')) FROM T2) ) SELECT SUBSTR(XMLAGG(XMLELEMENT(X, ',', TOBE) ORDER BY ROW_NUM).EXTRACT('//text()'), 2) TOBE_9I, TO_CHAR(WM_CONCAT(TOBE)) TOBE_10G, LISTAGG(TOBE, ',') WITHIN GROUP(ORDER BY ROW_NUM) TOBE_11G FROM T1 INNER JOIN ( SELECT TXT, REGEXP_SUBSTR(TXT, '[^,]+', 1, ROW_NUM) ASIS, ROW_NUM FROM ( SELECT ROW_NUM, TXT FROM T2 CROSS JOIN T3 ) ) A ON (T1.ASIS = A.ASIS) GROUP BY TXT
WITH t1(asis, tobe) AS ( SELECT '홍길동' asis, 'A1' tobe FROM dual UNION ALL SELECT '길말자', 'B7' FROM dual UNION ALL SELECT '김치' , 'C4' FROM dual UNION ALL SELECT '감자' , 'K3' FROM dual ) , t2(idx, txt) AS ( SELECT 1 idx, '홍길동,김치' txt FROM dual UNION ALL SELECT 2, '김치,감자' FROM dual UNION ALL SELECT 3, '감자' FROM dual UNION ALL SELECT 4, '홍길동,길말자,김치' FROM dual ) SELECT b.idx , b.txt , LISTAGG(a.tobe, ',') WITHIN GROUP(ORDER BY INSTR(','||b.txt||',', ','||a.asis||',')) x FROM t1 a , t2 b WHERE INSTR(','||b.txt||',', ','||a.asis||',') > 0 GROUP BY b.idx, b.txt ORDER BY b.idx ;