Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server 2005 for SQL2k Developer (Part 1)

By M. Choirul Amri,

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 :)

Total article views: 13911 | Views in the last 30 days: 13
 
Related Articles
FORUM

Pivot

Pivot

FORUM

Pivot Query- need help

Pivot

FORUM

subtotal

calculated column subtotal in a report

FORUM

pivot table subtotal

hello , how subtotal from pivot table my scrip ; select [HESAP KODU],[HESAP ADI],ISNULL(Oca...

FORUM

Pivot

Count Distinct in PIVOT

Tags
sql server 2005    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones