[DATABASE/ORACLE] 구분자 값을 갖는 컬럼을 행으로 분리하기

■ 구분자 값을 갖는 컬럼을 행으로 분리하는 방법을 보여준다.

▶ 예제 코드 (SQL)


WITH TEMPORARY_TABLE AS
(
    SELECT 1 ID, 'A|B|C' TXT FROM DUAL UNION ALL
    SELECT 2 ID, 'B|C'   TXT FROM DUAL UNION ALL
    SELECT 3 ID, 'A|C'   TXT FROM DUAL UNION ALL
    SELECT 4 ID, 'A|B|C' TXT FROM DUAL UNION ALL
    SELECT 5 ID, 'B|C'   TXT FROM DUAL UNION ALL
    SELECT 6 ID, 'B|C|D' TXT FROM DUAL
)
SELECT
    A.ID
   ,B.TXT
FROM TEMPORARY_TABLE A,
(
    SELECT DISTINCT REGEXP_SUBSTR(AA.TXT, '[^|]+', 1, LEVEL) TXT
    FROM
    (
        SELECT REPLACE(WM_CONCAT(DISTINCT AAA.TXT), ',', '|') TXT
        FROM TEMPORARY_TABLE AAA
    ) AA
    CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(AA.TXT, '[^|]+', '')) + 1
) B
WHERE INSTR('|' || A.TXT || '|', '|' || B.TXT || '|') > 0
ORDER BY A.ID, B.TXT;

/*
ID TXT
-- ---
1  A
1  B
1  C
2  B
2  C
3  A
3  C
4  A
4  B
4  C
5  B
5  C
6  B
6  C
6  D
*/

WITH TEMPORARY_TABLE AS

(

SELECT 1 ID, 'A|B|C' TXT FROM DUAL UNION ALL

SELECT 2 ID, 'B|C' TXT FROM DUAL UNION ALL

SELECT 3 ID, 'A|C' TXT FROM DUAL UNION ALL

SELECT 4 ID, 'A|B|C' TXT FROM DUAL UNION ALL

SELECT 5 ID, 'B|C' TXT FROM DUAL UNION ALL

SELECT 6 ID, 'B|C|D' TXT FROM DUAL

)

SELECT

A.ID

,B.TXT

FROM TEMPORARY_TABLE A,

(

SELECT DISTINCT REGEXP_SUBSTR(AA.TXT, '[^|]+', 1, LEVEL) TXT

FROM

(

SELECT REPLACE(WM_CONCAT(DISTINCT AAA.TXT), ',', '|') TXT

FROM TEMPORARY_TABLE AAA

) AA

CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(AA.TXT, '[^|]+', '')) + 1

) B

WHERE INSTR('|' || A.TXT || '|', '|' || B.TXT || '|') > 0

ORDER BY A.ID, B.TXT;

ID TXT

-- ---

1 A

1 B

1 C

2 B

2 C

3 A

3 C

4 A

4 B

4 C

5 B

5 C

6 B

6 C

6 D