[Oracle] 오라클 - 피벗 (PIVOT과 UNPIVOT)

[Oracle]
오라클 - 피벗 (PIVOT과 UNPIVOT)

✔️ PIVOT 절

: 열을 행으로 ROW 형태의 데이터를 COLUMN 형태로 보여주는 쿼리다.

: 컬럼의 값을 기준으로 새로운 컬럼을 도출하여 다양한 집계함수를 적용 가능하다.

: aggregate_function (expr) 그룹함수가 적용된 컬럼을 정의

: pivot_for_clause : 피벗의 기준이 되는 컬럼을 정의

: pivot_in_clause : 피벗 정의에 의해 정의한 컬럼의 필터링 값을 의미

-- 예제

WITH temp_table AS (
         SELECT 1 cnt, 1000 price FROM DUAL UNION ALL
         SELECT 2 cnt, 1050 price FROM DUAL UNION ALL
         SELECT 3 cnt, 2100 price FROM DUAL UNION ALL
         SELECT 1 cnt, 5500 price FROM DUAL UNION ALL
         SELECT 2 cnt, 7000 price FROM DUAL UNION ALL
         SELECT 3 cnt, 7000 price FROM DUAL
     )
     SELECT cnt, SUM(price) price
     FROM temp_table
     GROUP BY cnt;

WITH temp_table AS (
         SELECT 1 cnt, 1000 price FROM DUAL UNION ALL
         SELECT 2 cnt, 1050 price FROM DUAL UNION ALL
         SELECT 3 cnt, 2100 price FROM DUAL UNION ALL
         SELECT 1 cnt, 5500 price FROM DUAL UNION ALL
         SELECT 2 cnt, 7000 price FROM DUAL UNION ALL
         SELECT 3 cnt, 7000 price FROM DUAL
     )
     SELECT SUM(DECODE(cnt,1,price,0)) "1",
     SUM(DECODE(cnt,2,price,0)) "2",
     SUM(DECODE(cnt,3,price,0)) "3"
     
     FROM temp_table;

💡 행열 바꾸는데 아래처럼 해도 되지만, 피벗 사용해도 괜찮음

-- 피벗으로 행열 바꾸기

WITH temp_table AS (
         SELECT 1 cnt, 1000 price FROM DUAL UNION ALL
         SELECT 2 cnt, 1050 price FROM DUAL UNION ALL
         SELECT 3 cnt, 2100 price FROM DUAL UNION ALL
         SELECT 1 cnt, 5500 price FROM DUAL UNION ALL
         SELECT 2 cnt, 7000 price FROM DUAL UNION ALL
         SELECT 3 cnt, 7000 price FROM DUAL
     )
SELECT * FROM (
	SELECT cnt, price FROM temp_table
)
PIVOT (
	SUM(price) FOR cnt IN (1,2,3)
);

💡 pivot  (   출력할 값   ) FOR 행이 될 열 기준 (1,2,3)

🔒 문제

1) 부서별 출신도 인원수

SELECT dept, city, COUNT(*)
FROM emp
GROUP BY dept, city
ORDER BY dept;

-- 열값이던 부서를 행으로 가져가고 부서 인원수를 COUNT(dpet)

SELECT * FROM (
    SELECT city, dept
    FROM emp
)
PIVOT
(
    COUNT(dept)
    FOR dept IN (
        '개발부' 개발부,
        '기획부' 기획부,
        '영업부' 영업부,
        '인사부' 인사부,
        '자재부' 자재부,
        '총무부' 총무부,
        '홍보부' 홍보부
        )
      
);

2) 출신도에 대한 부서의 직위별 인원수

SELECT * FROM (
    SELECT city, dept, pos
    FROM emp
)
PIVOT
(   
    COUNT(dept) 
    FOR dept IN (
        '개발부' 개발부,
        '기획부' 기획부,
        '영업부' 영업부,
        '인사부' 인사부,
        '자재부' 자재부,
        '총무부' 총무부,
        '홍보부' 홍보부
        ) 
);

3) 월별 부서별 입사 인원수

SELECT * FROM (
    SELECT TO_NUMBER(TO_CHAR(hireDate, 'MM')) 월별 , dept
    FROM emp
    )
    PIVOT (
        COUNT(월별)
        FOR 월별 IN (1,2,3,4,5,6,7,8,9,10,11,12)
 
    );

💡 행에는 '월'이 찍히도록 하고
열값은 부서를 줘서 월별 / 부서 입사 인원수를 구할 수 있다. 

✔️ UNPIVOT 절

: 피벗을 반대로 설정하는 것이다.

CREATE TABLE tcity AS
    SELECT * FROM (
    SELECT city, dept, pos
    FROM emp
)
PIVOT
(   
    COUNT(dept) 
    FOR dept IN (
        '개발부' 개발부,
        '기획부' 기획부,
        '영업부' 영업부,
        '인사부' 인사부,
        '자재부' 자재부,
        '총무부' 총무부,
        '홍보부' 홍보부
        ) 
);
    
    SELECT * FROM tcity;
    
    -- 컬럼 단위를 행 단위로 변경
    SELECT * FROM tcity
    UNPIVOT
    (
        인원수
        FOR 부서 IN(개발부, 기획부, 영업부, 인사부, 자재부, 총무부, 홍보부)
    );