SQL Cookbook, 2nd Edition

Chapter 12. Reporting and Reshaping

This chapter introduces queries you may find helpful for creating reports. These typically involve reporting-specific formatting considerations along with different levels of aggregation. Another focus of this chapter is transposing or pivoting result sets: reshaping the data by turning rows into columns.

In general, these recipes have in common that they allow you to present data in formats or shapes different from the way they are stored. As your comfort level with pivoting increases, you’ll undoubtedly find uses for it outside of what are presented in this chapter.

12.1 Pivoting a Result Set into One Row

Problem

You want to take values from groups of rows and turn those values into columns in a single row per group. For example, you have a result set displaying the number of employees in each department:

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

You would like to reformat the output so that the result set looks as follows:

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

This is a classic example of data presented in a different shape than the way it is stored.

Solution

Transpose the result set using CASE and the aggregate function SUM:

1 select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
2        sum(case when deptno=20 then 1 else 0 end) as deptno_20,
3        sum(case when deptno=30 then 1 else 0 end) as deptno_30
4   from emp

Discussion

This example is an excellent introduction to pivoting. The concept is simple: ...