Cross Table Queries, SQL Server Pivot

In our project we often use Cross table queries, usually for generating statistical data, or even reports.
For Pivoting.

 Pivot was introduced in SQL Server 2005.
It was a surprise to know with Microsoft Access there is a built in option to produce crosstab queries, but not so with SQL Server 2000.
Pivot in SQL Server is used for cross table queries. Pivot was introduced in SQL Server 2005.
It was a surprise to know with Microsoft Access there is a built in option to produce crosstab queries, but not so with SQL Server 2000.
Here we are gonna see,
· What are cross table queries?
· How to use Pivot?

What are cross table queries? AND How to use Pivot in MS SQL Server?
A cross tab query is a transformation of rows of data to columns. It usually involves aggregation of data e.g. products broken down by colours, Class etc., where the Colours/Class are represented by columns. It's very hard to visualize without an example, so we will provide one below.
Let’s say you have a table of Products in [AdventureWorks] DB that looks like this
For query

SELECT    [Class] , [Name],COLOR
FROM [AdventureWorks].[Production].[Product]
where Color in 
('Black','Blue','Grey','Multi','Red','Silver','White','Yellow')
and Class is not null

Class	Name	Color
M	ML Crankarm	Black
H	HL Road Frame - Black, 58	Black
H	HL Road Frame - Red, 58	Red
L	LL Road Frame - Black, 60	Black
L	LL Road Frame - Red, 44	Red
M	ML Road Frame - Red, 52	Red
M	ML Road Frame - Red, 58	Red
M	ML Road Frame - Red, 60	Red

One example of a cross-tab is to take the above data and number of Products W.R.T color or Class. You want your resulting data to look like the below.

1. Number of Products in each class.

H	L	M
69	74	50

Select *
FROM
(
SELECT    [Class] , [Name]
FROM [AdventureWorks].[Production].[Product]
where Color in ( 'Black','Blue','Grey','Multi','Red','Silver','White','Yellow')
and Class is not null
)tab
PIVOT( Count([Name]) 
FOR [Class] IN( [H],[L],[M]              )
)  pivoted

2. Number of Products for each Colors

Black	Blue	Grey	Multi	Red	Silver	White	Yellow
93	26	1	8	38	43	4	36

Select *
FROM
(
SELECT    [Color] , [Name]
FROM [AdventureWorks].[Production].[Product]
where Color in ( 'Black','Blue','Grey','Multi','Red','Silver','White','Yellow')
)tab
PIVOT( Count([Name]) 
FOR [Color] IN( [Black],[Blue],[Grey],[Multi],[Red]
,[Silver],[White],[Yellow]
)
)  pivoted

3. Product Names W.R.T Colour and Class

Class	Black	Blue	Grey	Multi	Red	Silver	White	Yellow
NULL	Women's Tights, S	Sport-100 Helmet, Blue	Touring-Panniers, Large	Men's Bib-Shorts, S	Sport-100 Helmet, Red	Rear Derailleur Cage	Racing Socks, M	Short-Sleeve Classic Jersey, XL
H	Road-250 Black, 58	Touring-1000 Blue, 60	NULL	NULL	Road-250 Red, 58	Mountain-200 Silver, 46	NULL	Touring-1000 Yellow, 60
L	Road-750 Black, 58	Touring-3000 Blue, 62	NULL	NULL	Road-650 Red, 62	Mountain-500 Silver, 52	NULL	Touring-3000 Yellow, 62
M	Mountain-300 Black, 48	Touring-2000 Blue, 60	NULL	NULL	Road-450 Red, 60	Mountain-400-W Silver, 46	NULL	Road-550-W Yellow, 48
NULL	Women's Tights, S	Sport-100 Helmet, Blue	Touring-Panniers, Large	Men's Bib-Shorts, S	Sport-100 Helmet, Red	Rear Derailleur Cage	Racing Socks, M	Short-Sleeve Classic Jersey, XL
H	Road-250 Black, 58	Touring-1000 Blue, 60	NULL	NULL	Road-250 Red, 58	Mountain-200 Silver, 46	NULL	Touring-1000 Yellow, 60

Select *
FROM
(
SELECT    [Color] , [Name],Class
FROM [AdventureWorks].[Production].[Product]
where Color in ( 'Black','Blue','Grey','Multi','Red','Silver','White','Yellow')
)tab
PIVOT( MAX([Name]) 
FOR [Color] IN( [Black],[Blue],[Grey],[Multi],[Red]
,[Silver],[White],[Yellow]
)
)  pivoted

4. Product Name for particular Class and Particular Colour

H	Color
Road-250 Black, 58	Road-250 Black, 58
NULL	Road-750 Black, 58
NULL	Mountain-300 Black, 48
NULL	Women's Tights, S

Select [H] ,[BLACK]
FROM
(
SELECT    [Color] , [Name],Class
FROM [AdventureWorks].[Production].[Product]
where Color in ( 'Black','Blue','Grey','Multi','Red','Silver','White','Yellow')
UNION
SELECT    Class as [Color] , [Name], Class
FROM [AdventureWorks].[Production].[Product]
where Color in ( 'Black') 
)tab
PIVOT( MAX([Name]) 
FOR [Color] IN( [Black],[Blue],[Grey],[Multi],[Red]
,[Silver],[White],[Yellow],[H])

)  pivoted

Above queries where created in MS SQL Server 2008 and DB [AdventureWorks] on table

[Production].[Product] .