오라클 PIVOT(피봇)함수 사용예제

1. 미사용

WITH TEST AS (
SELECT 1 CNT, 100 WON FROM DUAL UNION ALL
SELECT 2 CNT, 105 WON FROM DUAL UNION ALL
SELECT 3 CNT, 210 WON FROM DUAL UNION ALL
SELECT 1 CNT, 550 WON FROM DUAL UNION ALL
SELECT 2 CNT, 700 WON FROM DUAL UNION ALL
SELECT 3 CNT, 700 WON FROM DUAL)
SELECT CNT

           , SUM(WON) WON
FROM TEST
GROUP BY CNT

2. 사용

 

WITH TEST AS (
SELECT 1 CNT, 100 WON FROM DUAL UNION ALL
SELECT 2 CNT, 105 WON FROM DUAL UNION ALL
SELECT 3 CNT, 210 WON FROM DUAL UNION ALL
SELECT 1 CNT, 550 WON FROM DUAL UNION ALL
SELECT 2 CNT, 700 WON FROM DUAL UNION ALL
SELECT 3 CNT, 700 WON FROM DUAL)
SELECT *
FROM ( (SELECT CNT, WON
             FROM TEST)
             PIVOT( SUM(WON)
                        FOR CNT IN(1,2,3)

                      )
          )

1	2	3
650	805	910