1. Home
  2. Knowledge Base
  3. Database Administration
  4. 11g – using the PIVOT and UNPIVOT commands

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
The following two tabs change content below.

Gavin Soorma

Latest posts by Gavin Soorma (see all)

Updated on June 2, 2021

Was this article helpful?

Related Articles

Leave a Comment