Recipe12.6.Pivoting a Result Set to Facilitate Inter-Row Calculations

Recipe 12.6. Pivoting a Result Set to Facilitate Inter-Row Calculations

Problem

You wish to make calculations involving data from multiple rows. To make your job easier, you wish to pivot those rows into columns such that all values you need are then in a single row.

In this book's example data, DEPTNO 20 is the department with the highest combined salary, which you can confirm by executing the following query:

  select deptno, sum(sal) as sal   from emp  group by deptno DEPTNO        SAL ------ ----------     10       8750     20      10875     30       9400 

You want to calculate the difference between the salaries of DEPTNO 20 and DEPTNO 10 and between DEPTNO 20 and DEPTNO 30.

Solution

Transpose the totals using the aggregate function SUM and a CASE expression. Then code your expressions in the select list:

 1 select d20_sal - d10_sal as d20_10_diff, 2        d20_sal - d30_sal as d20_30_diff 3   from ( 4 select sum(case when deptno=10 then sal end) as d10_sal, 5        sum(case when deptno=20 then sal end) as d20_sal, 6        sum(case when deptno=30 then sal end) as d30_sal 7   from emp 8        ) totals_by_dept 

Discussion

The first step is to pivot the salaries for each DEPTNO from rows to columns by using a CASE expression:

  select case when deptno=10 then sal end as d10_sal,        case when deptno=20 then sal end as d20_sal,        case when deptno=30 then sal end as d30_sal   from emp D10_SAL    D20_SAL    D30_SAL ------- ---------- ----------                800                          1600                          1250               2975                          1250                          2850    2450               3000    5000                          1500               1100                           950               3000    1300 

The next step is to sum all the salaries for each DEPTNO by applying the aggregate function SUM to each CASE expression:

  select sum(case when deptno=10 then sal end) as d10_sal,        sum(case when deptno=20 then sal end) as d20_sal,        sum(case when deptno=30 then sal end) as d30_sal   from emp D10_SAL    D20_SAL    D30_SAL ------- ---------- ----------    8750      10875       9400 

The final step is to simply wrap the above SQL in an inline view and perform the subtractions.