GeekPhilosopher: Pivot

Description:	Allows actual data to be "pivoted" into columns. Aggregate processing is then performed on the pivot columns.
Sql Server Version:	2005+.

Note: This sample uses the AdventureWorks database which can be downloaded here

Sample Pivot Query: The pivot query contains a "sourceTable" subquery and a "pivotTable" pivot query.

select Product,
           Northwest,
           Northeast,
           Central,
           Southwest,
           Southeast
from
(select terr.Name as Territory, prod.Name as Product, prod.ProductID ProductID
from Sales.SalesOrderHeader soh
inner join Sales.SalesOrderDetail sod on sod.SalesOrderID = soh.SalesOrderID
inner join Production.Product prod on prod.ProductID = sod.ProductID
inner join Sales.SalesTerritory terr on terr.TerritoryID = soh.TerritoryID
where terr.Name in ('Northwest', 'Northeast', 'Central', 'Southwest', 'Southeast')) as sourceTable
pivot
(count(ProductID)
for Territory in ([Northwest], [Northeast], [Central], [Southwest], [Southeast])) as pivotTable
order by product

1.	The "sourceTable" subQuery has a where clause requiring SalesTerritory.Name values to be in 'Northwest', 'Northeast', 'Central', 'Southwest', or 'Southeast'. This "where" clause is for illustration purposes only, the pivot does not require it.

"sourceTable" SubQuery:
select terr.Name as Territory, prod.Name as Product, prod.ProductID ProductID
from Sales.SalesOrderHeader soh
inner join Sales.SalesOrderDetail sod on sod.SalesOrderID = soh.SalesOrderID
inner join Production.Product prod on prod.ProductID = sod.ProductID
inner join Sales.SalesTerritory terr on terr.TerritoryID = soh.TerritoryID
where terr.Name in ('Northwest', 'Northeast', 'Central', 'Southwest', 'Southeast'

The results from this query includes:

Territory	Product	ProductID
Northwest	Sport-100 Helmet, Red	707
Northeast	Sport-100 Helmet, Red	707
Southwest	Sport-100 Helmet, Red	707
Southwest	Sport-100 Helmet, Red	707
Southeast	Sport-100 Helmet, Red	707
Northwest	Sport-100 Helmet, Red	707
Southwest	Sport-100 Helmet, Red	707
Southeast	Sport-100 Helmet, Red	707
Southeast	Sport-100 Helmet, Red	707
Northwest	Sport-100 Helmet, Red	707
Northeast	Sport-100 Helmet, Red	707
Central	Sport-100 Helmet, Black	708
Southwest	Sport-100 Helmet, Black	708
Northwest	Sport-100 Helmet, Blue	711

2.	The "pivotTable" query selects and aggregates the SalesTerritory.Name column values

select Product,
           Northwest,
           Northeast,
           Central,
           Southwest,
           Southeast
from
<< sourceTable results detailed in previous box >>
pivot
(count(ProductID)
for Territory in ([Northwest], [Northeast], [Central], [Southwest], [Southeast])) as pivotTable
order by product

3.	The following resuls are returned from pivot query. The SalesTerritory.Name column values that were specified in the pivot query have been aggregated into counts.

Product	Northwest	Northeast	Central	Southwest	Southeast
All-Purpose Bike Stand	36	0	0	49	0
AWC Logo Cap	412	110	129	664	124
Bike Wash - Dissolver	206	38	37	257	36
Cable Lock	27	27	28	62	25
Chain	35	22	13	44	13
Classic Vest, L	32	2	1	46	1
Classic Vest, M	60	32	31	99	30
Classic Vest, S	80	44	48	129	41
Fender Set - Mountain	418	1	1	566	3
Front Brakes	38	19	15	51	16
Front Derailleur	41	17	10	47	11
Full-Finger Gloves, L	55	29	31	74	47
Full-Finger Gloves, M	58	28	30	70	45
Full-Finger Gloves, S	43	14	18	32	38