Recipe12.3.Reverse Pivoting a Result Set

Recipe 12.3. Reverse Pivoting a Result Set

Problem

You want to transform columns to rows. Consider the following result set:

 DEPTNO_10  DEPTNO_20  DEPTNO_30 ---------- ---------- ----------          3          5          6 

You would like to convert that to:

 DEPTNO COUNTS_BY_DEPT ------ --------------     10              3     20              5     30              6 

Solution

Examining the desired result set, it's easy to see that you can execute a simple COUNT and GROUP BY on table EMP to produce the desired result. The object here, though, is to imagine that the data is not stored as rows; perhaps the data is denormalized and aggregated values are stored as multiple columns.

To convert columns to rows, use a Cartesian product. You'll need to know in advance how many columns you want to convert to rows because the table expression you use to create the Cartesian product must have a cardinality of at least the number of columns you want to transpose.

Rather than create a denormalized table of data, the solution for this recipe will use the solution from the first recipe of this chapter to create a "wide" result set. The full solution is as follows:

  1 select dept.deptno,  2        case dept.deptno  3             when 10 then emp_cnts.deptno_10  4             when 20 then emp_cnts.deptno_20  5             when 30 then emp_cnts.deptno_30  6        end as counts_by_dept  7   from (  8 select sum(case when deptno=10 then 1 else 0 end) as deptno_10,  9        sum(case when deptno=20 then 1 else 0 end) as deptno_20, 10        sum(case when deptno=30 then 1 else 0 end) as deptno_30 11   from emp 12        ) emp_cnts, 13        (select deptno from dept where deptno <= 30) dept 

Discussion

The inline view EMP_CNTS represents the denormalized view, or "wide" result set that you want to convert to rows, and is shown below:

  select sum(case when deptno=10 then 1 else 0 end) as deptno_10,        sum(case when deptno=20 then 1 else 0 end) as deptno_20,        sum(case when deptno=30 then 1 else 0 end) as deptno_30   from emp DEPTNO_10 DEPTNO_20  DEPTNO_30 --------- ---------- ----------         3          5          6 

Because there are three columns, you will create three rows. Begin by creating a Cartesian product between inline view EMP_CNTS and some table expression that has at least three rows. The following code uses table DEPT to create the Cartesian product; DEPT has four rows:

  select dept.deptno,        emp_cnts.deptno_10,        emp_cnts.deptno_20,        emp_cnts.deptno_30   from ( Select sum(case when deptno=10 then 1 else 0 end) as deptno_10,        sum(case when deptno=20 then 1 else 0 end) as deptno_20,        sum(case when deptno=30 then 1 else 0 end) as deptno_30   from emp        ) emp_cnts,        (select deptno from dept where deptno <= 30) dept DEPTNO DEPTNO_10  DEPTNO_20  DEPTNO_30 ------ ---------- ---------- ---------     10          3          5         6     20          3          5         6     30          3          5         6 

The Cartesian product enables you to return a row for each column in inline view EMP_CNTS. Since the final result set should have only the DEPTNO and the number of employees in said DEPTNO, use a CASE expression to transform the three columns into one:

  select dept.deptno,        case dept.deptno             when 10 then emp_cnts.deptno_10             when 20 then emp_cnts.deptno_20             when 30 then emp_cnts.deptno_30        end as counts_by_dept   from ( Select sum(case when deptno=10 then 1 else 0 end) as deptno_10,        sum(case when deptno=20 then 1 else 0 end) as deptno_20,        sum(case when deptno=30 then 1 else 0 end) as deptno_30   from emp        ) emp_cnts,        (select deptno from dept where deptno <= 30) dept DEPTNO COUNTS_BY_DEPT ------ --------------     10              3     20              5     30              6