SQL Server 2005 for SQL2k Developer (Part 1)

SQL Server 2005 comes with many enhancements for database developer. There are not only T-SQL enhancements, but also there are brand new features such as Service Broker. Reporting Services also has many improvements that make the database and business intelligence developer more productive. This article is the first in a series on SQL Server 2005 enhancements for developers. The main objective is providing guidance for the SQL Server 2000 developer for dealing with the 2005 changes.

PIVOT and UNPIVOT

This operator provides the capability to transform rows into columns. Developers are already familiar with this task in Analysis Services or Excel Pivot tables. In the past, you should use the CASE function to do this. Dealing with CASE in many rows and columns can be a painful task, so it is why PIVOT comes to make it easier.

Let’s build the sample data in a SalesSummarry table, that taken from AdventureWorks database sample:

Use AdventureWorks
CREATE TABLE Sales.SalesSummarry
(
OrderYear int NOT NULL,
SalesPersonID int NOT NULL,
SubTotal money NOT NULL
)
GO
INSERT INTO Sales.SalesSummarry
SELECT Year(OrderDate), 
SalesPersonID, SubTotal FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL

After the table is build you will have the following data:

OrderYear   SalesPersonID SubTotal
----------- ------------- ---------------------
2001        279           24643.9362
2001        279           1553.1035
2001        282           39422.1198
2001        282           34689.5578
2001        276           503.3507

In the past, you get the cross tab style result with the following script:

SELECT SalesPersonID,
SUM(CASE when OrderYear = 2001 then SubTotal ELSE 0 end) as '2002',
SUM(CASE when OrderYear = 2003 then SubTotal ELSE 0 end) as '2003',
SUM(CASE when OrderYear = 2004 then SubTotal ELSE 0 end) as '2004'
FROM Sales.SalesSummarry
GROUP BY SalesPersonID

It will return a cross tab result that compares SalesPersonID sales for every years. Using CASE function can be a daunting task when you have many rows to be classified and transformed to columns. But you can do it with the following PIVOT operator automatically:

SELECT * FROM Sales.SalesSummarry
PIVOT(SUM(SubTotal) FOR OrderYear IN
([2002],[2003],[2004])) as SalesPivot

Maybe you are wonder why should use * in SELECT, don’t worry because you can mention the columns as follows:

SELECT SalesPersonID, [2002],[2003],[2004] FROM Sales.SalesSummarry
PIVOT(SUM(SubTotal) FOR OrderYear IN
([2002],[2003],[2004])) as SalesPivot
ORDER BY SalesPersonID

The basic idea of this operation is sum up the measurement (which is SubTotal) for each changes of OrderYear.

You can also do UNPIVOT to extract the data that has been summarized. Run this script to make a table and sample data to be extrated:

Use AdventureWorks
CREATE TABLE SalesByPerson
(
SalesPersonID int NOT NULL,
[2002] int NULL,
[2003] int NULL,
[2004] int NULL
)
INSERT INTO SalesByPerson
SELECT SalesPersonID, [2002],[2003],[2004] FROM Sales.SalesSummarry
PIVOT(SUM(SubTotal) FOR OrderYear IN
([2002],[2003],[2004])) as SalesPivot

It’s clearly straight forward that after make the sample table, then load it with the summarized data with PIVOT operator. Then you can do UNPIVOT with the following operation:

SELECT * FROM SalesByPerson
UNPIVOT(SubTotal FOR OrderYear IN
([2002],[2003],[2004])) as A

Using Table Variable for PIVOT Operation

You have another option to make the PIVOT operation without making additional table to store raw data before processing it with PIVOT. The table variable can be used to store temporary data, and then clean up after PIVOT operation is finished.

This script is a modification of the PIVOT operation above:

USE AdventureWorks
GO
DECLARE @SummarrySales TABLE
(
OrderYear int NOT NULL,
SalesPersonID int NOT NULL,
SubTotal money NOT NULL
);
INSERT INTO @SummarrySales
SELECT Year(OrderDate), 
SalesPersonID, SubTotal FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL;
SELECT * FROM @SummarrySales
PIVOT(SUM(SubTotal) FOR OrderYear IN
([2002],[2003],[2004])) as SalesPivot

You do it in one batch, create table variable, load it with raw data, then do PIVOT operation on it.

Happy SQLing 🙂