[ORACLE] PIVOT, UNPIVOT 함수 (행을 열로, 열을 행으로 변환)

데이터를 조작하다 보면 행을 열로 변환해야 하는 일이 발생합니다. 이때 PIVOT과 DECODE 두가지 방법이 존재한다.

PIVOT/UNPIVOT 함수는 ORACLE 11g부터 제공되며, 이전 버전에서는 DECODE 함수로 작업을 해야 한다.

이번에는 PIVOT에 대하여 알아보자.

• PVOT : 행을 열로 변환
• UNPIVOT : 열을 행으로 변환

 

PIVOT / UNPIVOT 개념

• PIVOT의 사전적 의미는 회전축이라는 뜻을 가지고 있다. 즉 회전축을 기준으로 데이터를 돌려 행을 열로 열을 행으로 바꾸는 것입니다. ORACLE에서의 PIVOT 엑셀에서의 PIVOT 기능과 동일하다.
• 아래 그림은 테이블을 피봇했을 때의 예시이며, DEPTNO를 기준으로 JOB의 행이 열로 변경되고 SAL의 평균이 집계된걸 확인할 수 있다.

출처:  https://developer-guide.com/oracle-행을-열로-변환-pivot-decode/

 

PIVOT 문법

PIVOT을 사용하기 위해 설정해야할 값으로 ‘집계함수(집계컬럼)’, ‘피봇컬럼’, ‘피봇컬럼값 AS 별칭’이 3가지가 있습니다. 아래는 함수 사용 문법과 설명입니다.

• “집계함수(집계컬럼)”은 사용할 집계함수와 집계할 컬럼을 지정
• “피봇컬럼”은 피봇할 컬럼, 즉 열이될 컬럼을 지정
• “피봇컬럼값 AS 별칭 … “은 “피봇컬럼”에서 필터하여 열이될 값을 지정

SELECT *
  FROM ( 피벗할 쿼리문 ) AS result
 PIVOT ( 그룹합수(집계컬럼) FOR 피벗대상컬럼 IN ([피벗컬럼값] ... ) AS pivot_result

 

PIVOT 예시

 

SELECT DEPTNO
     , PRESIDENT
     , CLERK
     , MANAGER
     , ANALYST
     , SALESMAN
  FROM (SELECT DEPTNO ,JOB ,SAL FROM EMP) -- 집계와 전환하지 않은 컬럼인 부서로 암묵적 그룹화
 PIVOT (AVG(SAL) FOR JOB IN ('PRESIDENT' AS PRESIDENT,
                             'CLERK'     AS CLERK,
                             'MANAGER'   AS MANAGER,
                             'ANALYST'   AS ANALYST,
                             'SALESMAN'  AS SALESMAN));

 

UNPIVOT 문법

• 컬럼별칭(값) : UNPIVOT을 할 때 열의 값을 표시할 컬럼명을 지정하는 부분. 사용자가 임의로 별칭 지정
• 컬럼별칭(열) : UNPIVOT을 할 때 열의 컬럼명이 행으로 표시될 때 해당 컬럼의 별칭. 사용자가 임의로 별칭 지정
• 피벗열명 : UNPIVOT 대상 컬럼명을 지정. 별칭을 지정할 때는 문자열('')로 부여

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

 

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년'))

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

 

 

[참고]

https://developer-guide.com/oracle-행을-열로-변환-pivot-decode/

https://gent.tistory.com/382