행 데이타를 열 데이타로 바꾸는 방법으로 효율성 좋은 Sum(Case..) 용법이 추천 및 자주 사용 되었다.
그런데 11g에서 Pivot 의 등장 으로 인해 Sum(Case..) 용법은 이해하기 어렵고, 직관적 이지도 효율적 이지도 않다고 말하고 있다. 나는 오래써서 이해가 잘되는데...
하여간 Pivot이 훨씬 더 간결 하기는 하다.
아래는 각각의 쿼리 예제 이다. 복붙 확인도 좋지만 Select 절 정도는 ...
-- Sum(Case..) 예제
WITH TAB AS(
SELECT '가' AS NM, 'AA' AS COL, 1 AS CN FROM DUAL UNION ALL
SELECT '가' AS NM, 'AA' AS COL, 2 AS CN FROM DUAL UNION ALL
SELECT '가' AS NM, 'BB' AS COL, 3 AS CN FROM DUAL UNION ALL
SELECT '가' AS NM, 'BB' AS COL, 4 AS CN FROM DUAL UNION ALL
SELECT '나' AS NM, 'CC' AS COL, 5 AS CN FROM DUAL UNION ALL
SELECT '나' AS NM, 'CC' AS COL, 6 AS CN FROM DUAL UNION ALL
SELECT '나' AS NM, 'CC' AS COL, 7 AS CN FROM DUAL UNION ALL
SELECT '나' AS NM, 'DD' AS COL, 8 AS CN FROM DUAL UNION ALL
SELECT '나' AS NM, 'DD' AS COL, 9 AS CN FROM DUAL
)
SELECT NM, SUM(CASE WHEN COL='AA' THEN CN ELSE 0 END) COLA
, SUM(CASE WHEN COL='BB' THEN CN ELSE 0 END) COLB
, SUM(CASE WHEN COL='CC' THEN CN ELSE 0 END) COLC
, SUM(CASE WHEN COL='DD' THEN CN ELSE 0 END) COLD
FROM TAB
GROUP BY NM
;
-- Pivot 예제
WITH TAB AS(
SELECT '가' AS NM, 'AA' AS COL, 1 AS CN FROM DUAL UNION ALL
SELECT '가' AS NM, 'AA' AS COL, 2 AS CN FROM DUAL UNION ALL
SELECT '가' AS NM, 'BB' AS COL, 3 AS CN FROM DUAL UNION ALL
SELECT '가' AS NM, 'BB' AS COL, 4 AS CN FROM DUAL UNION ALL
SELECT '나' AS NM, 'CC' AS COL, 5 AS CN FROM DUAL UNION ALL
SELECT '나' AS NM, 'CC' AS COL, 6 AS CN FROM DUAL UNION ALL
SELECT '나' AS NM, 'CC' AS COL, 7 AS CN FROM DUAL UNION ALL
SELECT '나' AS NM, 'DD' AS COL, 8 AS CN FROM DUAL UNION ALL
SELECT '나' AS NM, 'DD' AS COL, 9 AS CN FROM DUAL
)
SELECT *
FROM TAB PIVOT(SUM(CN) FOR COL IN('AA' COLA, 'BB' COLB, 'CC' COLC, 'DD' COLD))
;
Pivot 및 Unpivot
상세 설명은 아래 링크로 하지만 영어!
https://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html
그래서 아래는 한글 번역 링크!
http://www.dbguide.net/db.db?cmd=view&boardUid=13948&boardConfigUid=9&boardIdx=97