[DataBase] 행->열, 열->행 변환(크로스탭)

쿼리를 작성하다 보면 행을 열로 변환하거나 열을 행으로 변환하는 작업을 심심치 않게 해볼 수가 있다.

 

이 전까지는 DECODE와 집계함수를 이용해서 작업을 했다면,

오라클 11g부터는 이를 쉽게 할 수 있는 PIVOT 함수가 생겼다고 한다.

 

1. DECODE와 집계함수를 이용한 피벗(이전버전)

 

SELECT TO_CHAR(DTE, 'YY') || '년' YEAR , SUM(DECODE(TO_CHAR(DTE, 'MM'),'01', 1, 0)) AS "1월" , SUM(DECODE(TO_CHAR(DTE, 'MM'),'02', 1, 0)) AS "2월" , SUM(DECODE(TO_CHAR(DTE, 'MM'),'03', 1, 0)) AS "3월" , SUM(DECODE(TO_CHAR(DTE, 'MM'),'04', 1, 0)) AS "4월" , SUM(DECODE(TO_CHAR(DTE, 'MM'),'05', 1, 0)) AS "5월" , SUM(DECODE(TO_CHAR(DTE, 'MM'),'06', 1, 0)) AS "6월" , SUM(DECODE(TO_CHAR(DTE, 'MM'),'07', 1, 0)) AS "7월" , SUM(DECODE(TO_CHAR(DTE, 'MM'),'08', 1, 0)) AS "8월" , SUM(DECODE(TO_CHAR(DTE, 'MM'),'09', 1, 0)) AS "9월" , SUM(DECODE(TO_CHAR(DTE, 'MM'),'10', 1, 0)) AS "10월" , SUM(DECODE(TO_CHAR(DTE, 'MM'),'11', 1, 0)) AS "11월" , SUM(DECODE(TO_CHAR(DTE, 'MM'),'12', 1, 0)) AS "12월" FROM TEMP_TABLE GROUP BY TO_CHAR(DTE, 'YY') ORDER BY YEAR ;

 

2. PIVOT 함수를 이용(오라클 11g부터 제공)

 

WITH TEMP_TABLE AS ( SELECT (TO_DATE('20160101','YYYYMMDD') + LEVEL-1) DTE FROM DUAL CONNECT BY LEVEL-1 <= TO_DATE('20171231','YYYYMMDD') - TO_DATE('20160101','YYYYMMDD') ) SELECT * FROM ( SELECT TO_CHAR(DTE, 'YY') || '년' YEAR , DECODE (TO_CHAR(DTE, 'MM'), '01', '1월', '02', '2월', '03', '3월', '04', '4월', '05', '5월', '06', '6월', '07', '7월', '08', '8월', '09', '9월', '10', '10월', '11', '11월', '12', '12월') AS MON , DTE FROM TEMP_TABLE ) PIVOT ( COUNT (DTE) FOR MON IN ('1월', '2월', '3월', '4월', '5월', '6월', '7월', '8월', '9월', '10월', '11월', '12월') ) ORDER BY YEAR ;