oracle pivot 기능

오라클 피벗 기능

 

 

WITH

  TEST_TEMP AS (

      SELECT '2013' AS YEA, '01' AS MON, '10000' VAL FROM DUAL UNION ALL

      SELECT '2013' AS YEA, '02' AS MON, '20000' VAL FROM DUAL UNION ALL

      SELECT '2013' AS YEA, '03' AS MON, '30000' VAL  FROM DUAL UNION ALL

      SELECT '2013' AS YEA, '04' AS MON, '40000' VAL  FROM DUAL UNION ALL

      SELECT '2013' AS YEA, '05' AS MON, '50000' VAL  FROM DUAL UNION ALL

      SELECT '2013' AS YEA, '06' AS MON, '60000' VAL  FROM DUAL UNION ALL

      SELECT '2013' AS YEA, '07' AS MON, '70000' VAL  FROM DUAL UNION ALL

      SELECT '2013' AS YEA, '08' AS MON, '80000' VAL  FROM DUAL UNION ALL

      SELECT '2013' AS YEA, '09' AS MON, '90000' VAL  FROM DUAL UNION ALL

      SELECT '2013' AS YEA, '10' AS MON, '100000' VAL  FROM DUAL UNION ALL

      SELECT '2013' AS YEA, '11' AS MON, '110000' VAL  FROM DUAL UNION ALL

      SELECT '2013' AS YEA, '12' AS MON, '120000' VAL  FROM DUAL 

  )

   

  SELECT  *

    FROM  (SELECT  YEA

               ,  MON

               , VAL

            FROM  TEST_TEMP)

   PIVOT  (SUM(VAL)

           FOR MON IN ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'))

ORDER BY  YEA DESC;