Pivot in Oracle(11g)

Oracle pivot 사용법(11g부터 가능)

 

SQL> select * from (
  2      select job, sal, deptno
  3        from emp
  4  )
  5  pivot
  6  (
  7    SUM(sal) as sal, count(sal) as cnt
  8    for job in ('CLERK',  'MANAGER', 'ANALYST')
  9  )
 10  order by deptno
 11  ;

    DEPTNO 'CLERK'_SAL 'CLERK'_CNT 'MANAGER'_SAL 'MANAGER'_CNT 'ANALYST'_SAL 'ANALYST'_CNT
---------- ----------- ----------- ------------- ------------- ------------- -------------
        10        1300           1          2450             1                           0
        20        1900           2          2975             1          6000             2
        30         950           1          2850             1                           0

SQL>

 

line8 : group by 에 해당하는 필드(job)을 기재한다.

         in 안에는 title 분류들이 들어간다.