[Oracle] 오라클 PIVOT(피벗) 함수 사용법 (행을 열로 변환하는 방법)

오라클 11g부터 PIVOT 기능을 제공합니다.

기존 이하버전에서는 DECODE 함수를 이용하여 로우를 컬럼으로 변경하는 작업을 하였습니다. 

PIVOT 기능을 이용하면 DECODE의 복잡하고 비직관적인 코드를 조금 더 직관적으로 작성할 수 있습니다.

아쉬운 접은 PIVOT 기능을 사용하더라도 PIVOT을 할 컬럼을 미리 정의를 해 놓아야 한다는 점이다.

상황에 맞게 PIVOT를 사용할지 DECODE를 사용할지 결정해서 사용하면 될꺼 같습니다.

 

기본 문법

SELECT *
  FROM ( 피벗 대상 쿼리문 )
 PIVOT ( 그룹합수(집계컬럼) FOR 피벗컬럼 IN (피벗컬럼값 AS 별칭 ... )

---

PIVOT 사용법

직군별, 월별 입사 건수

SELECT * 
  FROM ( 
         SELECT job , TO_CHAR(hiredate, 'FMMM') || '월' hire_month 
           FROM emp 
       ) 
 PIVOT (
         COUNT(*) 
         FOR hire_month IN ('1월', '2월', '3월', '4월', '5월', '6월',
                            '7월', '8월', '9월', '10월', '11월', '12월') 
       )

 

피벗 컬럼 값(1월, 2월, 3월 ...)은 한번 지정하면 해당 값이 존재하지 않아도 해당 컬럼이 표시된다.

 

기본적인 방법으로 피벗 컬럼 값을 동적으로 바꿀 수는 없다. 해당 값을 동적으로 바꾸기 위해서는 동적 쿼리 등 다른 편법을 사용해야 한다. 

 

TO_CHAR('2020-09-16', 'MM') → '09'

TO_CHAR('2020-09-16', 'FMMM') → '9'

 

직군별, 부서코드별 급여 합계 (피벗컬럼 별칭 사용)

SELECT job 
     , d1 
     , d2 
     , d3 
  FROM ( 
         SELECT job 
              , deptno 
              , sal 
           FROM emp 
       ) 
 PIVOT ( 
         SUM(sal) FOR deptno IN ('10' AS d1, '20' AS d2, '30' AS d3) 
       )

 

피벗 컬럼 값에 별칭(d1, d2, d3 ...)을 지정하면 SELECT 절에서 해당 별칭을 컬럼처럼 사용할 수 있다.

 

DECODE 사용법 (10g 이하 버전)

직군별, 월별 입사 건수

SELECT job 
     , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '1', 1, 0)) "1월" 
     , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '2', 1, 0)) "2월" 
     , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '3', 1, 0)) "3월" 
     , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '4', 1, 0)) "4월" 
     , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '5', 1, 0)) "5월" 
     , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '6', 1, 0)) "6월" 
     , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '7', 1, 0)) "7월" 
     , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '8', 1, 0)) "8월" 
     , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '9', 1, 0)) "9월" 
     , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '10', 1, 0)) "10월" 
     , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '11', 1, 0)) "11월" 
     , SUM(DECODE(TO_CHAR(hiredate, 'FMMM'), '12', 1, 0)) "12월" 
  FROM emp 
 GROUP BY job

 

오라클 10g 이하 버전에서는 PIVOT 함수를 사용할 수 없으므로 DECODE  함수를 사용하여 행을 열로 변환이 가능하다.