③ 컬럼을 로우로 변환하기

이젠 반대로 ex7_2 테이블을 조회해 컬럼을 로우로 바꿔볼 텐데, 계층형 쿼리를 사용하면 쉽게 변환할 수 있다. 예를 들어 90번 부서에는 3명의 사원이 속해 있으므로 로우로 변환하면 3개의 로우가 만들어져야 한다. 그럼 어떻게 3개의 로우로 만들 수 있을까?

3명의 사원 이름이 구분자인 콤마(,)로 구분되어 있고 구분자 개수가 2인데, 맨 끝에 콤마를 한 개 더 붙여 3개로 만든 다음 이를 레벨로 활용하면 CONNECT BY LEVEL 구문으로 3개의 로우를 만들어 낼 수 있다.

입력

    SELECT empnames,
           LEVEL as lvl
     FROM ( SELECT empnames || ',' as empnames,
                   LENGTH(empnames) ori_len,
                   LENGTH(REPLACE(empnames, ',', '')) new_len
              FROM ex7_2
             WHERE department_id = 90
           )
     CONNECT BY LEVEL <= ori_len - new_len + 1;

결과

    EMPNAMES                                 LVL
    ---------------------------------------- --------
    Lex De Haan,NeenaKochhar,Steven King,    1
    Lex De Haan,NeenaKochhar,Steven King,    2
    Lex De Haan,NeenaKochhar,Steven King,    3

먼저 서브 쿼리를 살펴보면 ori_len은 3명의 원래 문자열 길이, new_len은 원 문자열에서 콤마를 제외한 문자열 길이를 반환한다. 따라서 (ori_len-new_len) 값에 1을 더하면 총 사원 수(3)가 되고 CONNECT BY LEVEL에 명시하면 위와 같이 3개의 로우를 만들어 낼 수 있다. 이제 할 일은 각 레벨별로 사원 이름을 분리해 내기만 하면 된다. 즉 1레벨은 맨 처음 사원, 2레벨은 두 번째, 3레벨은 세 번째 사원명을 분리해 내야 한다.

입력

    SELECT empnames,
           DECODE(level, 1, 1, INSTR(empnames, ',', 1, LEVEL-1)) start_pos,
           INSTR(empnames, ',', 1, LEVEL) end_pos,
           LEVEL as lvl
      FROM (  SELECT empnames || ',' as empnames,
                     LENGTH(empnames) ori_len,
                     LENGTH(REPLACE(empnames, ',', '')) new_len
                FROM ex7_2
               WHERE department_id = 90
            )
      CONNECT BY LEVEL <= ori_len - new_len + 1;

결과

    EMPNAMES                                START_POS    END_POS    LVL
    --------------------------------------- ------------ ---------- -------
    Lex De Haan,NeenaKochhar,Steven King,   1            12         1
    Lex De Haan,NeenaKochhar,Steven King,   12           26         2
    Lex De Haan,NeenaKochhar,Steven King,   26           38         3

콤마를 기준으로 레벨별로 start_pos는 시작 위치, end_pos는 끝 위치이며 이를 이용하면 다음과 같이 사원명을 분리해 낼 수 있다.

입력

    SELECT REPLACE(SUBSTR(empnames, start_pos, end_pos - start_pos), ',', '') AS emp
    FROM ( SELECT empnames,
                  DECODE(level, 1, 1, INSTR(empnames, ',', 1, level-1)) start_pos,
                  INSTR(empnames, ',', 1, LEVEL) end_pos,
                  LEVEL as lvl
          FROM (  SELECT empnames || ',' as empnames,
                         LENGTH(empnames) ori_len,
                         LENGTH(REPLACE(empnames, ',', '')) new_len
                    FROM ex7_2
                   WHERE department_id = 90
               )
          CONNECT BY LEVEL <= ori_len - new_len + 1
    ) ;

결과

    EMP
    ----------------
    Lex De Haan
    NeenaKochhar
    Steven King

start_pos와 end_pos 값을 활용해 문자열을 잘랐고 최종적으로 REPLACE 함수를 써서 콤마를 제거해 각 사원 이름을 추출해 냈다. 콤마로 구분된 문자열에서 각 사원명을 분리해 내는 로직이 약간 복잡하지만 차근차근 살펴보면 이해하는데 그리 어렵지 않을 것이다. 여기에서 중요한 점은 계층형 쿼리로 사원 수에 맞게 콤마를 한 개 추가하고 이 콤마 수를 레벨로 활용해 컬럼을 로우 형태로 만들어 낸 것이다.