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 EMP AS
(
SELECT 10 AS DEPTNO, 'CLERK' AS JOB, 7934 AS EMPNO, 'MILLER' AS ENAME, 1300 AS SAL, 200 AS BONUS FROM DUAL UNION ALL
SELECT 10 AS DEPTNO, 'MANAGER' AS JOB, 7782 AS EMPNO, 'CLARK' AS ENAME, 2450 AS SAL, 100 AS BONUS FROM DUAL UNION ALL
SELECT 10 AS DEPTNO, 'PRESIDENT' AS JOB, 7839 AS EMPNO, 'KING' AS ENAME, 5000 AS SAL, 0 AS BONUS FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'ANALYST' AS JOB, 7902 AS EMPNO, 'FORD' AS ENAME, 3000 AS SAL, 300 AS BONUS FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'ANALYST' AS JOB, 7788 AS EMPNO, 'SCOTT' AS ENAME, 3000 AS SAL, 600 AS BONUS FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'CLERK' AS JOB, 7876 AS EMPNO, 'ADAMS' AS ENAME, 1100 AS SAL, 200 AS BONUS FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'CLERK' AS JOB, 7369 AS EMPNO, 'SMITH' AS ENAME, 800 AS SAL, 200 AS BONUS FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'MANAGER' AS JOB, 7566 AS EMPNO, 'JONES' AS ENAME, 2975 AS SAL, 300 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'CLERK' AS JOB, 7900 AS EMPNO, 'JAMES' AS ENAME, 950 AS SAL, 300 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'MANAGER' AS JOB, 7698 AS EMPNO, 'BLAKE' AS ENAME, 2850 AS SAL, 400 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7521 AS EMPNO, 'WARD' AS ENAME, 1250 AS SAL, 500 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7844 AS EMPNO, 'TURNER' AS ENAME, 1500 AS SAL, 0 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7499 AS EMPNO, 'ALLEN' AS ENAME, 1600 AS SAL, 300 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7654 AS EMPNO, 'MARTIN' AS ENAME, 1250 AS SAL, 1400 AS BONUS FROM DUAL
)
SELECT DEPTNO,
JOB,
SUM(SAL) AS SUM_SAL, /* (부서 & 직함별) 급여 합 */
SUM(CASE WHEN JOB = 'ANALYST' /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
THEN SAL
END) AS SUM_SAL_ANALYST,
SUM(CASE WHEN JOB = 'CLERK' /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
THEN SAL
END) AS SUM_SAL_CLERK,
SUM(CASE WHEN JOB = 'MANAGER' /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
THEN SAL
END) AS SUM_SAL_MANAGER,
SUM(CASE WHEN JOB = 'SALESMAN' /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
THEN SAL
END) AS SUM_SAL_SALESMAN,
SUM(CASE WHEN JOB = 'PRESIDENT' /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
THEN SAL
END) AS SUM_SAL_PRESIDENT,
SUM(BONUS) AS SUM_BONUS, /* (부서 & 직함별) 보너스 합 */
COUNT(*) AS CNT /* (부서 & 직함별) 인원 수 */
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB
WITH절의 EMP 데이터
쿼리 결과
CASE 문을 DECODE로 변경 (CASE문 방식과 결과는 동일)
WITH EMP AS
(
...
...
...
)
SELECT DEPTNO,
JOB,
SUM(SAL) AS SUM_SAL, /* (부서 & 직함별) 급여 합 - NON-PIVOT */
SUM(DECODE(JOB, 'ANALYST', SAL)) AS SUM_SAL_ANALYST, /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
SUM(DECODE(JOB, 'CLERK', SAL)) AS SUM_SAL_CLERK, /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
SUM(DECODE(JOB, 'MANAGER', SAL)) AS SUM_SAL_MANAGER, /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
SUM(DECODE(JOB, 'SALESMAN', SAL)) AS SUM_SAL_SALESMAN, /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
SUM(DECODE(JOB, 'PRESIDENT', SAL)) AS SUM_SAL_PRESIDENT, /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
SUM(BONUS) AS SUM_BONUS, /* (부서 & 직함별) 보너스 합 - NON-PIVOT */
COUNT(*) AS CNT /* (부서 & 직함별) 인원 수 - NON-PIVOT */
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB
GROUP BY 조건을 변경(JOB을 그룹핑 제외)하여 쿼리 수행
WITH EMP AS
(
...
...
...
)
SELECT DEPTNO,
-- JOB, /* JOB을 그룹핑 제외 */
SUM(SAL) AS SUM_SAL,
SUM(CASE WHEN JOB = 'ANALYST'
THEN SAL
END) AS SUM_SAL_ANALYST,
...
...
...
SUM(BONUS) AS SUM_BONUS,
COUNT(*) AS CNT
FROM EMP
GROUP BY DEPTNO --, JOB /* JOB을 그룹핑 제외 */
ORDER BY DEPTNO --, JOB /* JOB을 그룹핑 제외 */
