Performing a pivot

Pivoting data is a typical requirement for data warehousing. Let's say that we want to compare salary grades across departments. Based on the SRC_EMP, SRC_DEPT, and SRC_SALGRADE tables described in the Preface of this book, we can run the following query:

select SRC_DEPT.DEPTNO, grade, count(grade)
  from SRC_EMP, SRC_DEPT, SRC_SALGRADE
  where SRC_EMP.DEPTNO=SRC_DEPT.DEPTNO
  and (SRC_EMP.SAL between SRC_SALGRADE.LOSAL and SRC_SALGRADE.HISAL)
  group by SRC_DEPT.DEPTNO, grade
  order by 1,2;

The query gives us the result we want, but not something that helps much with the comparison of data:

    DEPTNO      GRADE COUNT(GRADE)
---------- ---------- ------------
        10          2            1
        10          4            1
        10          5            1
        20          1            2
        20          4            3
        30          1            1
        30          2            2
        30          3            2
        30          4            1

What we are really looking ...

Get Oracle Data Integrator 11g Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.