오라클 column to row (컬럼의 데이터를 여러개의 row로 가져오기)

- 컬럼을 여러개의 로우로 표현하기

테이블의 데이터를 보면 한 컬럼안의 구분자를 사용하여 여러개의 데이터를 저장하여 사용하는 테이블이 있다.

SELECT '02,031,032' LIST_COL FROM DUAL

│    LIST_COL   │
│───────────────│
│  02,031,032   │
└───────────────┘

현재 LIST_COL 컬럼에는 한 컬럼안에 데이터가 ,(콤마)로 구분되어 코드값이 저장되어 있다.

1개의 로우로 표현된 컬럼 값을 ,(콤마)로 데이터를 분리하여 여러개의 ROW로 표현해보겠다.

1.  , (콤마)로 구분되어 코드값을 여러개의 ROW로 추출하기

     WITH LIST_TXT AS (SELECT '02,031,032' LIST_COL FROM DUAL)
   SELECT TRIM(REGEXP_SUBSTR(LIST_COL,'[^,]+',1,LEVEL)) AS  LIST_TO_ROW
     FROM LIST_TXT
CONNECT BY INSTR(LIST_COL, ',' , 1, LEVEL - 1 ) > 0

 

    SELECT TRIM(REGEXP_SUBSTR('02,031,032','[^,]+',1,LEVEL)) AS  LIST_TO_ROW
      FROM DUAL
 CONNECT BY INSTR('02,031,032', ',', 1, LEVEL-1 ) > 0

[결과]

│  LIST_TO_ROW  │
│───────────────│
│     02        │
├───────────────┤
│     031       │
├───────────────┤
│     032       │
└───────────────┘

이렇게 사용하면 여러개의 테이블에 필요한 데이터를 추출하고 싶을때 SUBQUERY를 사용하여 뽑아낼수 있다.

2. 응용하기 ( 데이터 리스트를 row로 표현하여 해당 데이터만 뽑아내기 )

    SELECT A.REGION
             , A.AREA_CODE
             , B.RESIDENCE_NAME
      FROM 
    (
        SELECT '000' AS P_CODE,'서울' AS REGION, '02' AREA_CODE FROM DUAL
        UNION ALL
        SELECT '000' AS P_CODE, '경기' AS REGION, '031' AREA_CODE FROM DUAL
        UNION ALL
        SELECT '000' AS P_CODE, '인천' AS REGION, '032' AREA_CODE FROM DUAL
        UNION ALL
        SELECT '000' AS P_CODE, '강원' AS REGION, '033' AREA_CODE FROM DUAL
        UNION ALL
        SELECT '000' AS P_CODE, '부산' AS REGION, '051' AREA_CODE FROM DUAL
    ) A
    INNER JOIN
    (
        SELECT '000' AS P_CODE, '조현영' AS RESIDENCE_NAME, '02,031,032' AS AREA_CODE_LIST FROM DUAL
    ) B
    ON A.P_CODE = B.P_CODE
    WHERE A.AREA_CODE IN(
        SELECT TRIM(REGEXP_SUBSTR(B.AREA_CODE_LIST,'[^,]+',1,LEVEL)) AS  LIST_TO_ROW
          FROM DUAL
     CONNECT BY INSTR(B.AREA_CODE_LIST, ',', 1, LEVEL-1 ) > 0
    )

임시로 A, B 테이블 데이터를 정의하고 

A테이블에는 지역과 지역번호 데이터가 있고 

B테이블에는 지역번호 데이터가 ,(콤마)로 구분되어 여러개 들어가 있다.

[A 테이블]

P_CODE   REGION  AREA_CODE
 000   │ 서울   │   02    │
 000   │ 경기   │  031    │
 000   │ 인천   │  032    │
 000   │ 강원   │  033    │
 000   │ 부산   │  051    │

[B 테이블]

P_CODE   RESIDENCE_NAME   AREA_CODE_LIST
 000   │    조현영        │   02,031,032  │

A테이블과 B테이블을 조인하여

조현영의 지역번호에 해당하는 지역명을 추출하려면 REGEXP_SUBSTR 정규식을 사용해 

한 컬럼에 저장된 리스트를 여러개의 row로 변환하여

in 조건절 안에 해당하는 데이터만 추출할 수 있는 것을 확인할 수 있다.

[결과]

REGION    AREA_CODE   RESIDENCE_NAME
 서울   │    02     │     조현영     │
 경기   │    031    │     조현영     │
 인천   │    032    │     조현영     │