11g – using the PIVOT and UNPIVOT commands

11g has a very good new feature for SQL*PLUS called PIVOT and UNPIVOT - it is very useful for
creating cross tabular reports and aggregates.

In earlier Oracle versions this would have needed a lot of coding using the DECODE function ....

Objective - find the count of employees in each department broken down by the job performed.

 SQL>
  select * from
    (select deptno,job from emp
    )
PIVOT
    (
    count(*)
  for job in ('SALESMAN','CLERK','MANAGER','ANALYST')
     )
;

SQL>
    DEPTNO 'SALESMAN'    'CLERK'  'MANAGER'  'ANALYST'
---------- ---------- ---------- ---------- ----------
        30          4          1          1          0
        20          0          2          1          2
        10          0          1          1          0

This operation can be reversed using the UNPIVOT command

SQL>
 select * from pivot_emp
  UNPIVOT
  (employee_count for job in ("'SALESMAN'","'CLERK'","'MANAGER'","'ANALYST'")
    );

    DEPTNO JOB        EMPLOYEE_COUNT
---------- ---------- --------------
        30 'SALESMAN'              4
        30 'CLERK'                 1
        30 'MANAGER'               1
        30 'ANALYST'               0
        20 'SALESMAN'              0
        20 'CLERK'                 2
        20 'MANAGER'               1
        20 'ANALYST'               2
        10 'SALESMAN'              0
        10 'CLERK'                 1
        10 'MANAGER'               1
        10 'ANALYST'               0
12 rows selected.

Objective - find count which year were most employees hired in

SQL> select empno,hiredate from emp;

     EMPNO HIREDATE
---------- ---------
      7369 17-DEC-80
      7499 20-FEB-81
      7521 22-FEB-81
      7566 02-APR-81
      7654 28-SEP-81
      7698 01-MAY-81
      7782 09-JUN-81
      7788 19-APR-87
      7839 17-NOV-81
      7844 08-SEP-81
      7876 23-MAY-87
      7900 03-DEC-81
      7902 03-DEC-81
      7934 23-JAN-82
14 rows selected.

SQL>
  select count(*), extract (year from hiredate) year_hired from emp
 group by  extract (year from hiredate);

  COUNT(*) YEAR_HIRED
---------- ----------
         1       1982
         2       1987
         1       1980
        10       1981

Please follow and like us: