[Oracle] 오라클 UNPIVOT 사용법 (열을 행으로 변환)

출처: https://gent.tistory.com/382

 

오라클 11g부터 PIVOT과 UNPIVOT 기능을 사용할 수 있다. PIVOT은 행을 열로 변환하는 기능이며, UNPIVOT은 열을 행으로 변환하는 기능이다. 아래는 UNPIVOT을 설명한 예제이다.

 

오라클 UNPIVOT

 

[Oracle] 오라클 PIVOT 사용법 (행을 열로 변환)

 

UNPIVOT을 처음 사용할 때는 많이 헷갈릴 수 있으니, 아래의 예제를 차근차근 따라 해 보면서 이해하면 된다.

 

SELECT *     FROM ( 피벗 대상 쿼리문 )  UNPIVOT ( 컬럼별칭(값) FOR 컬럼별칭(열) IN (피벗열명 AS '별칭', ... )

 

컬럼별칭(값) : UNPIVOT을 할 때 열의 값을 표시할 컬럼명을 지정하는 부분이다. 위에 예제에서 HIRE_CNT에 해당된다. 사용자가 임의로 별칭을 지정하면 된다.

컬럼별칭(열) : UNPIVOT을 할 때 열의 컬럼명이 행으로 표시될 때 해당 컬럼의 별칭이다. 위의 예제에서 HIRE_YEAR에 해당 된다. 사용자가 임의로 별칭을 지정하면 된다.

피벗열명 : UNPIVOT 대상 컬럼명을 지정하면 된다. 별칭을 지정할 때는 문자열('')로 부여하면 된다.

 

기본 예제

WITH temp AS (
    SELECT 1 AS col1, 2 AS col2, 3 AS col3 FROM dual
)

 SELECT col_nm
      , col_val
   FROM (
          SELECT *
            FROM temp
        )
UNPIVOT (col_val FOR col_nm IN (col1, col2, col3))

 

3개의 컬럼이 3개의 행으로 변환되었다. 컬럼 별칭(col_nm, col_val)은 내가 원하는 명칭으로 지정하면 된다.

 

심화 예제

WITH temp AS (
    SELECT 'TAB1' AS tab_nm, 1 AS col1, 2 AS col2, 3 AS col3 FROM dual UNION ALL
    SELECT 'TAB2' AS tab_nm, 4 AS col1, 5 AS col2, 6 AS col3 FROM dual
)

 SELECT tab_nm
      , col_nm
      , col_val
   FROM (
          SELECT *
            FROM temp
        )
UNPIVOT (col_val FOR col_nm IN (col1, col2, col3))

 

UNPIVOT 하지 않은 컬럼(tab_nm)의 값은 UNPIVOT 컬럼의 개수만큼 값이 복사되어 출력된다.

 

응용 예제

WITH emp_stat AS (
    SELECT 'ANALYST'   AS job, 0 AS Y_1980, 1 AS Y_1981, 0 AS Y_1982, 1 AS Y_1987 FROM dual UNION ALL
    SELECT 'CLERK'     AS job, 1 AS Y_1980, 1 AS Y_1981, 1 AS Y_1982, 1 AS Y_1987 FROM dual UNION ALL  
    SELECT 'SALESMAN'  AS job, 0 AS Y_1980, 4 AS Y_1981, 0 AS Y_1982, 0 AS Y_1987 FROM dual UNION ALL  
    SELECT 'MANAGER'   AS job, 0 AS Y_1980, 3 AS Y_1981, 0 AS Y_1982, 0 AS Y_1987 FROM dual UNION ALL  
    SELECT 'PRESIDENT' AS job, 0 AS Y_1980, 1 AS Y_1981, 0 AS Y_1982, 0 AS Y_1987 FROM dual    
)

SELECT job
     , hire_year
     , hire_cnt
  FROM (
         SELECT *
           FROM emp_stat
        )
UNPIVOT (hire_cnt for hire_year in (Y_1980 AS '1980년', 
                                    Y_1981 AS '1981년', 
                                    Y_1982 AS '1982년', 
                                    Y_1987 AS '1987년'))

 

쿼리는 조금 더 복잡해 보이지만 위의 예제들과 크게 다른 부분은 없다. UNPIVOT 컬럼의 별칭을 부여할 때는 문자열('')로 부여하면 해당 별칭이 값으로 출력된다.