오라클 PIVOT/UNPIVOT

/*
PIVOT/UNPIVOT 사용 (오라클 11g부터 지원)

열을 행으로
SUM(CNT) : 집계
MM  데이터를 가지고 피벗함 '01', '02','03'으로 집계하면서  
'01','02','03'이 컬럼 타이틀이 됨 
컬럼명을 바꾸고 싶을때는 ALIAS를 주어 변경함
*/

WITH T AS (
   SELECT '2013' AS BAS_YM, '01' AS MM, 1 AS CNT FROM DUAL
   UNION ALL
   SELECT '2013' AS BAS_YM, '01' AS MM, 1 AS CNT FROM DUAL
   UNION ALL
   SELECT '2013' AS BAS_YM, '02' AS MM, 1 AS CNT FROM DUAL
   UNION ALL
   SELECT '2013' AS BAS_YM, '03' AS MM, 1 AS CNT FROM DUAL
   UNION ALL
   SELECT '2014' AS BAS_YM, '03' AS MM, 1 AS CNT FROM DUAL
   UNION ALL
   SELECT '2014' AS BAS_YM, '01' AS MM, 1 AS CNT FROM DUAL
)

SELECT X.* FROM (
            SELECT A.BAS_YM,  A.MM,  A.CNT
              FROM T A
            WHERE 1-1
            ) PIVOT ( SUM(CNT) FOR MM IN (  '01',  '02',  '03'  )
 )X;



행을 열로
TEST2, TEST1 타이틀 ,  SELECT 절에  조회( A.BAS_YM, TO_CHAR(A.CNT)) 
조회 컬럼이랑 IN절에 BAS_YM AS '년월', CNT AS '월'의 1:1 타입이 맞아야함


WITH T AS (
   SELECT '2013' AS BAS_YM, '01' AS MM, 1 AS CNT FROM DUAL
   UNION ALL
   SELECT '2013' AS BAS_YM, '01' AS MM, 1 AS CNT FROM DUAL
   UNION ALL
   SELECT '2013' AS BAS_YM, '02' AS MM, 1 AS CNT FROM DUAL
   UNION ALL
   SELECT '2013' AS BAS_YM, '03' AS MM, 1 AS CNT FROM DUAL
   UNION ALL
   SELECT '2014' AS BAS_YM, '03' AS MM, 1 AS CNT FROM DUAL
   UNION ALL
   SELECT '2014' AS BAS_YM, '01' AS MM, 1 AS CNT FROM DUAL
)

SELECT X.* FROM (
                  SELECT A.BAS_YM, TO_CHAR(A.CNT)
                     FROM T A
                   WHERE 1-1
   ) UNPIVOT ( TEST2 FOR TEST1 IN ( BAS_YM AS '년월', CNT AS '월'  )
 )X;