O'Reilly logo

Oracle Data Integrator 11g Cookbook by Peter C. Boyd-Bowman, Denis Gray, Christophe Dupupet, Julien Testut

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required