PIVOT 절과 UNPIVOT 절

PIVOT은 회전시킨다는 의미를 갖고 있다.
PIVOT 절은 행을 열로, UNPIVOT 절은 열을 행으로 회전시킨다.

PIVOT 절

PIVOT 절의 구문은 아래와 같다.

PIVOT [XML]
	(aggregate_function (expr) [[AS] alias]
  [, aggregate_function (expr) [[AS] alias]]...
     FOR {column | (column [, column]...)}
     IN ({{{expr | (expr [, expr]...)} {[AS] alias]}...
     	| subquery
        | ANY [, ANY]...
        })
   )

aggregate_function은 집계할 열을 지정한다.
FOR 절은 PIVOT할 열을 지정한다.
IN 절은 PIVOT할 열 값을 지정한다.

다음은 PIVOT 절을 사용한 쿼리다.

SELECT 
	*
FROM (SELECT JOB, DEPTNO, SAL FROM EMP)
	PIVOT (SUM (SAL) FOR DEPTNO IN (10, 20, 30))
ORDER BY 1;

다음 쿼리는 별칭을 지정했다.

SELECT 
	JOB, D20_SAL
FROM (SELECT JOB, DEPTNO, SAL FROM EMP)
	PIVOT (SUM (SAL) AS SAL FOR DEPTNO IN (10 AS D10, 20 AS D20, 30 AS D30))
ORDER BY 1;

다수의 집계함수를 지원한다.

SELECT 
	*
FROM (SELECT JOB, DEPTNO, SAL FROM EMP)
	PIVOT (SUM (SAL) AS SAL
    	 , COUNT (*) AS CNT FOR DEPTNO IN (10 AS D10, 20 AS D20, 30 AS D30))
ORDER BY 1;

FOR 절에도 다수의 열을 기술할 수 있다.

SELECT 
	*
FROM (SELECT TO_CHAR(HIREDATE, 'YYYY') AS YYYY
	       , JOB, DEPTNO, SAL FROM EMP)
	PIVOT (SUM (SAL) AS SAL
    	 , COUNT (*) AS CNT
         FOR (DEPTNO, JOB) IN ((10, 'ANALYST') AS D10A
         					 , (10, 'CLERK') AS D10C
                             , (20, 'ANALYST') AS D20A
                             , (20, 'CLERK') AS D20C))
ORDER BY 1;

PIVOT 절을 사용할 수 없는 경우 집계함수와 CASE 표현식으로 PIVOT 가능하다.

SELECT JOB
	 , SUM(CASE DEPTNO WHEN 10 THEN SAL END) AS D10_SAL
     , SUM(CASE DEPTNO WHEN 20 THEN SAL END) AS D20_SAL
     , SUM(CASE DEPTNO WHEN 30 THEN SAL END) AS D30_SAL
FROM EMP
GROUP BY JOB
ORDER BY JOB;

UNPIVOT 절

UNPIVOT 절은 PIVOT 절과 반대로 동작한다.

UNPIVOT [{INCLUDE | EXCLUDE} NULLS]
	( {column | (column [, col]...)}
    	FOR {column | column [, col]...)}
        IN ({column | column [, col]...)} [AS {literal | literal [, literal] ...)}]
        [, {column | column [, col]...)} [AS {literal | literal [, literal] ...)}]...
        )
   )

UNPIVOT column 절은 UNPIVOT된 값이 들어갈 열을 지정한다.
FOR 절은 UNPIVOT된 값을 설명할 값이 들어갈 열을 지정한다.
IN 절은 UNPIVOT할 열과 설명할 값의 리터럴 값을 지정한다.

예제 테이블 생성

DROP TABLE T1 PURGE;
CREATE TABLE T1 AS
SELECT JOB, D10_SAL, D20_SAL, D10_CNT, D20_CNT
FROM (SELECT JOB, DEPTNO, SAL
	  FROM EMP
	  WHERE JOB IN ('ANALYST', 'CLERK'))
PIVOT(SUM (SAL) AS SAL
   , COUNT(*) AS CNT
   FOR DEPTNO IN (10 AS D10, 20 AS D20));

SELECT * FROM T1 ORDER BY JOB;

SELECT JOB, DEPTNO, SAL
FROM T1
UNPIVOT (SAL FOR DEPTNO IN (D10_SAL, D20_SAL))
ORDER BY 1, 2;

IN 절에 별칭을 지정하면 FOR 절에 지정한 열의 값을 변경할 수 있다.

SELECT JOB, DEPTNO, SAL
FROM T1
UNPIVOT (SAL FOR DEPTNO IN (D10_SAL AS 10, D20_SAL AS 20))
ORDER BY 1, 2;

다음과 같이 INCLUDE NULLS 키워드를 기술하면 UNPIVOT된 열의 값이 널인 행도 결과에 포함된다.

SELECT JOB, DEPTNO, SAL
FROM T1
UNPIVOT INCLUDE NULLS (SAL FOR DEPTNO IN (D10_SAL AS 10, D20_SAL AS 20))
ORDER BY 1, 2;

FOR 절에 다수의 열, IN 절에 다수의 별칭을 지정할 수 있다.

SELECT JOB, DEPTNO, SAL
FROM T1
UNPIVOT ((SAL, CNT)
		FOR (DEPTNO, DNAME) IN ((D10_SAL, D10_CNT) AS (10, 'A')
        			 , (D20_SAL, D20_CNT) AS (20, 'B')))
ORDER BY 1, 2;

UNPIVOT 절을 사용할 수 없는 경우 카티션 곱을 사용해 UNPIVOT을 수행할 수 있다.
UNPIVOT할 열의 개수만큼 행을 복제하고, CASE 표현식으로 UNPIVOT할 열을 선택하는 방식이다.

SELECT A.JOB
	 , CASE B.LV WHEN 1 THEN 10	WHEN 2 THEN 20 END AS DEPTNO
     , CASE B.LV WHEN 1 THEN A.D10_SAL	WHEN 2 THEN A.D20_SAL END AS SAL
     , CASE B.LV WHEN 1 THEN A.D10_CNT	WHEN 2 THEN A.D20_CNT END AS CNT
FROM T1 A
	, (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <= 2) B
ORDER BY 1, 2;

SELECT A.JOB
	 , B.LV
     , A.D10_SAL
     , A.D20_SAL
     , A.D10_CNT
     , A.D20_CNT
FROM T1 A
	, (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <= 2) B
ORDER BY A.JOB, B.LV;