데이터베이스 질문과 답변
매핑에 테이블에 따른 데이터 변환
3
264
by oratong [2016.10.23 12:52:25]
안녕하세요
고민되는 SQL이 있어서 고수님들께 질문을 드려야 할것 같아 올리게 되었습니다.
- 질문 -
다음의 매핑 테이블이 있습니다.
| asis | tobe |
| 홍길동 | A1 |
| 길말자 | B7 |
| 김치 | C4 |
| 감자 | K3 |
테이블의 데이터는 아래와 같습니다.
| 홍길동,김치 |
| 김치,감자 |
| 감자 |
| 홍길동,길말자,김치 |
1. 테이블의 데이터는 사용자명 을 ',' 구분자로 여러건이 한 로우에 들어가 있음
2. '사용자명' --> '사용자ID' 로 매핑 테이블에 정의 된 TOBE 데이터로 변경하고 싶습니다.
3. 변경후 데이터
| A1,C4 |
| C4,K3 |
| K3 |
| A1,B7,C4 |
감사합니다.^^
by Kyle
[2016.10.23 16:31:38]
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
by 마농
[2016.10.24 07:41:05]
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
;
by oratong
[2016.11.06 01:57:41]
정말 감사드립니다.
많은 도움이 되었습니다.
언제나 좋은 답변 들어서 좋은데.. 저도 좋은 답을 올릴 실력이 되었으면 좋겠네요