[Oracle] 행 => 열 변환 PIVOT (PIVOT 사용)

[실습] (부서 & 직함별)로 자녀 수 합, 급여 합, 보너스 합 조회           ※ 급여 합, 보너스 합은 행을 열로 변환(PIVOT)하여 표현

 

WITH EMPP AS 
(
	SELECT 10 AS DEPTNO, 'CLERK'     AS JOB, 7934 AS EMPNO, 'MILLER' AS ENAME, 1300 AS SAL, 200  AS BONUS, 3 AS CHLDN FROM DUAL UNION ALL
	SELECT 10 AS DEPTNO, 'MANAGER'   AS JOB, 7782 AS EMPNO, 'CLARK'  AS ENAME, 2450 AS SAL, 100  AS BONUS, 0 AS CHLDN FROM DUAL UNION ALL
	SELECT 10 AS DEPTNO, 'PRESIDENT' AS JOB, 7839 AS EMPNO, 'KING'   AS ENAME, 5000 AS SAL, 0    AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'ANALYST'   AS JOB, 7902 AS EMPNO, 'FORD'   AS ENAME, 3000 AS SAL, 300  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'ANALYST'   AS JOB, 7788 AS EMPNO, 'SCOTT'  AS ENAME, 3000 AS SAL, 600  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'CLERK'     AS JOB, 7876 AS EMPNO, 'ADAMS'  AS ENAME, 1100 AS SAL, 200  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'CLERK'     AS JOB, 7369 AS EMPNO, 'SMITH'  AS ENAME, 800  AS SAL, 200  AS BONUS, 4 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'MANAGER'   AS JOB, 7566 AS EMPNO, 'JONES'  AS ENAME, 2975 AS SAL, 300  AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'CLERK'     AS JOB, 7900 AS EMPNO, 'JAMES'  AS ENAME, 950  AS SAL, 300  AS BONUS, 0 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'MANAGER'   AS JOB, 7698 AS EMPNO, 'BLAKE'  AS ENAME, 2850 AS SAL, 400  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7521 AS EMPNO, 'WARD'   AS ENAME, 1250 AS SAL, 500  AS BONUS, 0 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7844 AS EMPNO, 'TURNER' AS ENAME, 1500 AS SAL, 0    AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7499 AS EMPNO, 'ALLEN'  AS ENAME, 1600 AS SAL, 300  AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7654 AS EMPNO, 'MARTIN' AS ENAME, 1250 AS SAL, 1400 AS BONUS, 3 AS CHLDN FROM DUAL
),
/* NON-PIVOT 결과 */
A AS (
   SELECT DEPTNO,
          JOB,
          SUM(CHLDN) AS CHLDN
   FROM EMPP
   GROUP BY DEPTNO, JOB
),
/* PIVOT 결과 */
B AS 
(
	SELECT *
	FROM 
	(
		SELECT 
		       DEPTNO, -- GROUP BY 
		       JOB,    -- GROUP BY (JOIN 불가)
		       JOB J,  -- JOIN용
		       SAL,    -- SUM(SAL)
		       BONUS   -- SUM(BONUS)
		   FROM EMPP
	)
	PIVOT
	(
		SUM(SAL) AS SAL, 
		SUM(BONUS) AS BNS
		FOR JOB IN ( 'CLERK' AS C, 
                      'ANALYST' AS A,
                       'PRESIDENT' AS P,
                       'SALESMAN' AS S,
                       'MANAGER' AS M
                       )
	) 
) /* End of B */
SELECT A.DEPTNO AS A_DEPTNO,
       A.JOB AS A_JOB,
       A.CHLDN,
       B.*
FROM A,  /* NON-PIVOT 결과 */ 
     B   /* PIVOT 결과 */
WHERE A.DEPTNO = B.DEPTNO
  AND A.JOB = B.J
ORDER BY A_DEPTNO, A_JOB;

WITH절의 EMPP 테이블 데이터

 

쿼리 결과

 

참조
http://www.yes24.com/Product/Goods/46218018
http://blog.naver.com/PostView.nhn?blogId=silentis&logNo=220943488844