programming4us
programming4us
DATABASE

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

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
6/7/2011 11:50:43 AM
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
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.

Other  
  •  SQL Server 2008 : Transact-SQL Programming - Ranking Functions
  •  SQL Server 2008 : Transact-SQL Programming - Common Table Expressions
  •  SQL Server 2008 : Transact-SQL Programming - The OUTPUT Clause
  •  SQL Server 2008 : Transact-SQL Programming - TOP Enhancements
  •  SQL Server 2008 : Transact-SQL Programming - The max Specifier
  •  SQL Server 2008 : T-SQL Tips and Tricks (part 3) - Generating T-SQL Statements with T-SQL & De-Duping Data with Ranking Functions
  •  SQL Server 2008 : T-SQL Tips and Tricks (part 2) - Using CONTEXT_INFO & Working with Outer Joins
  •  SQL Server 2008 : T-SQL Tips and Tricks (part 1) - Date Calculations & Sorting Results with the GROUPING Function
  •  SQL Server 2008 : General T-SQL Performance Recommendations
  •  SQL Server 2008 : General T-SQL Coding Recommendations (part 2) - Avoid SQL Injection Attacks When Using Dynamic SQL & Comment Your T-SQL Code
  •  
    programming4us
     
     
    programming4us