[DB/ORACLE] PIVOT ( UNPIVOT절 예정 )

--PIVOT 절과 UNPIVOT절
-- PIVOT 절은 행을 열로 회전시키고
-- UNPIVOT절은 열을 행으로 회전 시킨다.

-- 기본문법
-- 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절을 사용한 쿼리이다. PIVOT 절은 집계 함수와 FOR절에 지정되지 않는 열을 기준으로
-- 인라인 뷰를 통해 사용할 열을 지정해야 한다.
SELECT
    *
FROM 
    (
        SELECT JOB, DEPTNO, SAL FROM EMP
    )
PIVOT ( SUM(SAL) FOR DEPTNO IN ( 10, 20 , 30))
ORDER BY 1;

-- 아래 쿼리는 인라인 뷰에 YYYY표현식을 추가했다. 
-- 행 그룹에 YYYY 표현식이 추가된것을 확인할 수 있다.
SELECT
    *
FROM 
    (
        SELECT JOB, TO_CHAR(HIREDATE, 'YYYY') AS YYYY, DEPTNO, SAL FROM EMP
    )
PIVOT ( SUM(SAL) FOR DEPTNO IN (10,20,30))
ORDER BY 1,2;

-- 아래 쿼리는 집계함수와  IN절에 별칭을 지정했다.
-- 별칭을 지정하면 결과 집합의 열명이 변경된다.
SELECT 
    *
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;

-- 집계함수와 IN절에 지정한 별칭에 따라 다음과 같은 규칙으로 열명이 부여된다.
-- 집계함수와 IN절 모두 별칭을 지정하는 편이 바람직 하다.

	10	10 AS D10
SUM(SAL)	10	D10
SUM(SAL) AS SAL	10_SAL	D10_SAL

-- SELECT 절에 부여된 열명을 지정하면 필요한 열만 조회할 수 있다
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;

-- PIVOT절은 다수의 집계 함수를 지원한다. 아래 쿼리는 SUM함수와 COUNT함수를 함께 사용했다
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))
ORDER BY 1;


-- FOR절에도 다수의 열을 기술할 수 있다. 아래와 같이 IN절에 다중열을 사용해야한다
SELECT
    *
FROM
    (
        SELECT TO_CHAR(HIREDATE, 'YYYY') AS YYYY, DEPTNO, JOB, 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 절에 XML 키워드를 기술하면 XML 포맷으로 결과가 반환된다. XML 키워드를 사용하면 IN절에 서브쿼리와 ANY키워드를 사용할 수 있다.
SELECT
    *
FROM ( SELECT JOB, DEPTNO, SAL FROM EMP )
PIVOT XML( SUM(SAL) AS SAL FOR DEPTNO IN (SELECT DEPTNO FROM DEPT))
ORDER BY 1;

-- ANY 키워드는 존재하는 값과 일치하는 와일드 카드로 동작한다. 아래 쿼리에서 ANY키워드는 10,20,30 과 일치한다.
SELECT
    *
FROM (SELECT JOB, DEPTNO, SAL FROM EMP)
PIVOT XML (SUM(SAL) AS SAL FOR DEPTNO IN (ANY))
ORDER BY 1;

-- XML 키워드를 기술해도 다수의 집계 함수와 다수의 열을 사용 할 수 있다.
SELECT
    *
FROM (SELECT JOB, DEPTNO, SAL FROM EMP)
PIVOT XML( SUM(SAL) AS SAL, COUNT(*) AS CNT FOR (DEPTNO, JOB) IN (ANY, ANY));

-- 기존방식
SELECT
    JOB
    , SUM(DECODE(DEPTNO, 10, SAL)) AS D10_SAL
    , SUM(DECODE(DEPTNO, 20, SAL)) AS D20_SAL
    , SUM(DECODE(DEPTNO, 30, SAL)) AS D30_SAL
FROM EMP
GROUP BY JOB
ORDER BY JOB;