Oracle Data Integrator 11g Cookbook

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 ...