SQL Server 2008 : Transact-SQL Programming - PIVOT and UNPIVOT

SQL Server 2005 added the PIVOT clause to T-SQL. A typical analytical use of the PIVOT command is to convert temporal data into categorized data to make the data easier to view and analyze.

The first, and simplest, option for returning the temporal data is to query the data, grouping the fact information in such a way that it answers the questions being asked. For example, Listing 1 retrieves data from the titles tables and provides the total number of business books sold, by year. Note that it’s difficult to see trends of the number of titles sold between years. It is also even more difficult to answer questions about how sales of one title compared to sales of other titles in the same year.

Listing 1. A Standard Query to Return Total Sales of Titles, by Year

select t.title_id, datepart(year, ord_date) as year, sum(qty) as total_sales from sales s join titles t on s.title_id = t.title_id where t.type = 'business' group by t.title_id, datepart(year, ord_date) go title_id year total_sales -------- ----------- ----------- BU7832 2007 104616 BU1111 2007 88116 BU2075 2007 117888 BU1032 2006 95556 BU7832 2008 78240 BU1111 2006 104149 BU2075 2008 84588 BU7832 2006 102975 BU1032 2007 97131 BU2075 2006 108995 BU1032 2008 59772 BU1111 2008 72336

You could visualize and answer these questions more easily if you could pivot the YEAR column to create columns of TOTAL_SALES for each year. The resulting table is generally referred to as a crosstab. The PIVOT clause provides this capability.

The syntax of the PIVOT expression is as follows:

pivoted_table ::=
     table_source PIVOT ( aggregate_function ( value_column )
        FOR pivot_column
        IN ( column_list ) table_alias

To use the PIVOT feature, you first decide which column contains the important values for the query. In this example, the important piece of information is the total_sales amount. Next, you determine which field data becomes the columns you will pivot the data into. In this example, because you want to analyze sales over a period of time, you want to pivot the sales year field data into columns in the final result.

You start out by defining a CTE that returns the detail data on which you want to aggregate. This CTE might look like the following SQL code fragment:

with title_sales as
(select t.title_id,
       datepart(year, ord_date) as year,
       qty
from sales s join titles t on s.title_id = t.title_id
where t.type = 'business')

Drawing from the title_sales CTE, the value column is the qty column. Because you want to sum the qty values, you need to use the SUM() aggregate function in the PIVOT expression. The pivot column is YEAR. You need to define a list of YEAR columns that you want to see. For this example, the columns are 2006, 2007, and 2008. You specify these values as column headings in the select list in the SQL expression and also as the column list in the PIVOT expression. Putting all the pieces together, you end up with the SQL statement shown in Listing 2.

Listing 2. Using PIVOT to Return Total Sales by Year

with title_sales as (select t.title_id, datepart(year, ord_date) as year, qty as total_sales from sales s join titles t on s.title_id = t.title_id where t.type = 'business') select ts_pivot.title_id, isnull([2006], 0) as [2006], isnull([2007], 0) as [2007], isnull([2008], 0) as [2008] from title_sales pivot (sum(total_sales) for year in ([2006], [2007], [2008]) ) as ts_pivot go title_id 2006 2007 2008 -------- ----------- ----------- ----------- BU1032 95556 97131 59772 BU1111 104149 88116 72336 BU2075 108995 117888 84588 BU7832 102975 104616 78240

Note that in this example, the SUM aggregate function is a required component of the PIVOT expression. If you think about it, the CTE could easily have been coded to perform the sum of the qty values, grouping them by year and simply have the PIVOT expression pivot using only the total_sales, without the need for the SUMPIVOT expression requires having an aggregate function included, so it must be coded this way. function. Unfortunately, the

Also note that the data specified in the IN column list for the PIVOT expression must explicitly include the names of all the values that will be pivoted into columns. Currently, the syntax does not allow for this column list to be dynamic.

The UNPIVOT expression is used to take data that is already in the form of a crosstab and rotate the data columns into data rows. You are likely to use UNPIVOTPIVOT. much less often than

For example, you can create a temporary table from the results of the PIVOT query in Listing 43.55:

with title_sales as
(select t.title_id,
       datepart(year, ord_date) as year,
       qty
from sales s join titles t on s.title_id = t.title_id
where t.type = 'business')
select ts_pivot.title_id,
       isnull([2006], 0) as [2006],
       isnull([2007], 0) as [2007],
       isnull([2008], 0) as [2008]
into #title_sales_by_year
from title_sales
pivot (sum(qty) for year in ([2006], [2007], [2008])
) as ts_pivot
go
select title_id, [2006], [2007], [2008]
    from #title_sales_by_year
go


title_id 2006        2007        2008
-------- ----------- ----------- -----------
BU1032   95556       97131       59772
BU1111   104149      88116       72336
BU2075   108995      117888      84588
BU7832   102975      104616      78240


					  

To unpivot the #title_sales_by_year table, you start with the common table expression that returns the rows and columns you want to unpivot:

with title_sales as
(select title_id, [2006], [2007], [2008]
    from #title_sales_by_year)

Drawing from the title_sales CTE, the year columns become a single column called year, and you rotate the current total_sales columns into a single column. This time around, the year column is the unpivot column. For this example, the columns you want to unpivot are 2006, 2007, and 2008. You specify these values as the columnUNPIVOT expression and rename it as a single column called total_sales. Putting all the pieces together, you end up with the SQL statement shown in Listing 3. list in the

Listing 3. Using UNPIVOT to Rotate Pivoted Data

Code View: Scroll / Show All with title_sales as (select title_id, [2006], [2007], [2008] from #title_sales_by_year) select title_id, cast(ts_unpivot.year as smallint) as year, ts_unpivot.total_sales from title_sales UNPIVOT (total_sales for year in ([2006], [2007], [2008]) ) as ts_unpivot go title_id year total_sales -------- ------ ----------- BU1032 2006 95556 BU1032 2007 97131 BU1032 2008 59772 BU1111 2006 104149 BU1111 2007 88116 BU1111 2008 72336 BU2075 2006 108995 BU2075 2007 117888 BU2075 2008 84588 BU7832 2006 102975 BU7832 2007 104616 BU7832 2008 78240

Note

UNPIVOT is not the exact reverse of PIVOT. PIVOT performs an aggregation and, therefore, merges possible multiple rows into a single row in the output. UNPIVOT does not reproduce the original detail rows from the summary values.