SQL Pocket Guide, 3rd Edition

Pivoting and Unpivoting

Oracle Database (11g Release 1 onward) and SQL Server (2005 onward) both support pivot and unpivot operators. With the PIVOT operation, you can present data in a grid format by turning rows into columns while aggregating some value of interest. The UNPIVOT operation turns columns into rows, allowing you to take multiple columns containing the same type of data and present that data as one column for reporting or analysis.

Pivoting: The Concept

Use the pivot operation to create a lookup table presenting values termed measures at the intersection of other values, termed dimensions. For example, say that you wish to report on the number of falls open or closed to the public by county. You might begin with the following query:

SELECT county_id,
       COALESCE(open_to_public, 'n')
          AS open_to_public,
       COUNT(id)
FROM upfall
GROUP BY county_id, open_to_public;

And you would get row-by-row results like these:

COUNTY_ID O  COUNT(ID)
---------- - ----------
***        n          1
        11 y          1
         2 y         11
         6 y          1
         7 y          2
        10 y          1
         9 y          1

Looking carefully at the output, you can see that county #7 has two falls open to the public. The information is all there, but the presentation is cumbersome and not at all compact.

A more useful presentation might be the following grid, which allows you to scan down to find the county, and then over to find the number of open and closed falls within that county:

COUNTY_ID       Open     Closed
---------- ---------- ----------
***                 0          1
         6          1          0
        11          1          0
         2         11          0
         7          2          0
         9          1          0
        10          1          0

You can use the PIVOT ...