오라클 PIVOT함수 사용예제

1) PIVOT함수 미사용

SQL	result
WITH TEST AS ( SELECT 1 CNT, 1000 WON FROM DUAL UNION ALL SELECT 2 CNT, 1050 WON FROM DUAL UNION ALL SELECT 3 CNT, 2100 WON FROM DUAL UNION ALL SELECT 1 CNT, 5500 WON FROM DUAL UNION ALL SELECT 2 CNT, 7000 WON FROM DUAL UNION ALL SELECT 3 CNT, 7000 WON FROM DUAL) SELECT CNT            , SUM(WON) WON FROM TEST GROUP BY CNT	CNT WON 1 6500 2 8050 3 9100

 

2) PIVOT함수 사용

SQL

result

WITH TEST AS ( SELECT 1 CNT, 1000 WON FROM DUAL UNION ALL SELECT 2 CNT, 1050 WON FROM DUAL UNION ALL SELECT 3 CNT, 2100 WON FROM DUAL UNION ALL SELECT 1 CNT, 5500 WON FROM DUAL UNION ALL SELECT 2 CNT, 7000 WON FROM DUAL UNION ALL SELECT 3 CNT, 7000 WON FROM DUAL) SELECT * FROM ( (SELECT CNT, WON              FROM TEST)              PIVOT( SUM(WON)                         FOR CNT IN(1,2,3)                       )           )

1 2 3 6500 8050 9100