PIVOT , UNPIVOT

PIVOT

PIVOT 은 회전시킨다는 의미를 갖고 있다.

PIVOT 절은 행을 열로 회전시킨다.

PIVOT [ XML ] 
(
    aggregate_function (expr) [[AS] ALIAS]  // 집계할 열을 지정
    [,aggregate_function (expr) [[AS] ALIAS] ]
    FOR { column | (column[,column..])} // PIVOT 할 열을 지정
    IN ({   { {expr | (expr[,expr]..)} [[AS] alias ]} // PIVOT 할 열 값을 지정
    		| subquery
            | ANY [,ANY]
       })
)

aggregate function : 집계할 열을 지정

FOR : PIVOT 할 열을 지정

IN : PIVOT 할 열 값을 지정

SELECT * 
FROM (
    SELECT JOB, DEPTNO, SAL 
    FROM EMP
    PIVOT ( SUM(SAL)  // 집계할 열을 지정
    FOR DEPTNO 	    // PIVOT할 열을 지정	
    IN (10,20,30)  // PIVOT할 열 값을 지정	
    )
ORDER BY 1;    

===============================================
JOB           10     20     30
-----        ----   ----    ----
ANALYST             6000 
CLERK        1300   1900     950
MANAGER      2450   2975    2850
PRESIDENT    5000
SALESMAN                    5000

집계함수과 IN 절에 지정한 별칭에 따라 아래와 같은 규칙으로 열 명이 부여된다.

집계함수와 IN 절 모두 별칭을 지정하는 것이 바람직하다.

	10	10 as D10
SUM(SAL)	10	D10
SUM(SAL) AS sal	10_sal	D10_sal

 

조건문 부여 예시 

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)
    WHERE D20_SAL > 2500
    )
ORDER BY 1;    

===============================================
JOB          D20_SAL
-----        --------
ANALYST      6000 
MANAGER      2975

    

다수의 집계함수 사용 예시 

SELECT JOB, D20_SAL
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;    

===============================================
JOB          D10_SAL   D10_CNT   D20_SAL   D20_CNT
-----        -------   -------   -------  -------
ANALYST                    0      6000        2
CLERK          1300        1      1900        2
MANAGER        2450        1      2975        1
    		...

 

FOR 절에 다수의 열 기술 예지 

SELECT JOB, D20_SAL
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;    

===================================================
YYYY      D10A_SAL   D10A_CNT  D10C_SAL   D10C_CNT   D20A_SAL   D20A_CNT  D20C_SAL   D20C_CNT
-----     --------   --------  --------   --------   --------   --------  --------   --------
1980                  
1981           					...............
1982       
    		

 

PIVOT 대신 집계함수와  CASE 표현식 사용

================================================================
CASE 문 
================================================================
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;

================================================================
PIVOT 절
================================================================
SELECT * 
FROM (
    SELECT JOB, DEPTNO, SAL 
    FROM EMP
    PIVOT ( SUM(SAL) 
    FOR DEPTNO 	    
    IN (10 AS D10, 20 AS D20, 30 AS D30)
    )
ORDER BY 1;    
================================================================
JOB       D10_SAL   D20_SAL   D30_SAL
-----     -------   -------   -------
ANALYST             6000 
CLERK        1300   1900        950
MANAGER      2450   2975       2850
PRESIDENT    5000
SALESMAN                       5000

---

UNPIVOT

UNPIVOT 절은 열을 행으로 회전시킨다.

UNPIVOT [ { INCLUE | EXCLUDE } NULLS ] 
	( { column | (column[,col..]..) } // UNPIVOT 된 값이 들어갈 열을 지정
   		FOR { column | (column[,col..]) } // UNPIVOT 된 값을 설명할 값이 들어갈 열을 지정
    	IN ( 					// UNPIVOT할 열과 설명할 값의 리터럴 값을 지정
    		{ column | (column[,col..]) } AS { literal | literal[,literal]..}
        	,[{ column | (column[,col..]) } AS { literal | literal[,literal]..}]
    		)
    )

UNPIVOT column : UNPIVOT 된 값이 들어갈 열을 지정

FOR 절 : UNPIVOT 된 값을 설명할 값이 들어갈 열을 지정

IN 절 : UNPIVOT할 열과 설명할 값의 리터럴 값을 지정

 

//테이블 T1 내부 

SELECT * FROM T1 ORDER BY JOB;
===========================================
JOB       D10_SAL   D20_SAL   D30_SAL
-----     -------   -------   -------
ANALYST             6000 
CLERK        1300   1900        950
MANAGER      2450   2975       2850
PRESIDENT    5000
SALESMAN                       5000

SELECT JOB, DEPTNO, SAL
FROM T1
UNPIVOT (
	SAL // UNPIVOT 된 값이 들어갈 열을 지정
    FOR DEPTNO  //  UNPIVOT 된 값을 설명할 값이 들어갈 열을 지정
    IN (D10_SAL, D20_SAL) // UNPIVOT할 열과 설명할 값의 리터럴 값을 지정
    )
ORDER BY 1,2;

===================================================
JOB       DEPTNO     SAL
======    ======     ====
ANALYST   D20_SAL    6000
CLERK     D10_SAL    1300
CLERK     D20_SAL    1900

 

UNPIVOT 도 PIVOT 과 마찬가지로 다수의 집계 함수를 지원하고 다수의 열을 가질 수 있다.

 

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;

===================================================
JOB       DEPTNO     SAL     CNT
======    ======     ====    ====
ANALYST      10                0
ANALYST      20      6000      0
CLERK        10      1300      2
CLERK        20      1900      1

---

출처

SQL 전문가 가이드 - 한국데이터산업진흥원