By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP

October 1, 2005

Crosstab reports are very popular with management. They are like spreadsheets. Resorting to data warehouse terminology, a crosstab report tabulates the cross section measure of two dimensions. In the sample below, one dimension is the year of order; the other dimension is the product. The cross section measure is the dollar value of the orders.

You can construct a crosstab report in previous versions of SQL Server, however, the PIVOT feature of SQL Server 2005 comes really handy for crosstab reports. Also, note the extensive use of converts to bring the figures to dollar format.

Crosstab reports, as opposed to flat reports, are not dynamic. Comes next year, you have go back and modify the script or stored procedure for year 2006. Built-in job security feature!

This is the code sample:

USE AdventureWorks
GO
WITH ProductOrder(ProductName, OrderYear, OrderTotal)
AS (
SELECT p.Name,
YEAR(soh.OrderDate),
sod.LineTotal
FROM Production.Product p
JOIN Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
)

SELECT ProductOrderPIVOT.ProductName,
[2001] = '$'+convert(varchar(14),convert(money,ISNULL([2001], 0)),1),
[2002] = '$'+convert(varchar(14),convert(money,ISNULL([2002], 0)),1),
[2003] = '$'+convert(varchar(14),convert(money,ISNULL([2003], 0)),1),
[2004] = '$'+convert(varchar(14),convert(money,ISNULL([2004], 0)),1),
[2005] = '$'+convert(varchar(14),convert(money,ISNULL([2005], 0)),1)
FROM ProductOrder
PIVOT (
SUM(OrderTotal)
FOR OrderYear IN ([2001], [2002], [2003], [2004], [2005])
) AS ProductOrderPIVOT
ORDER BY ProductOrderPIVOT.ProductName

This is the partial result set:

ProductName	2001	2002	2003	2004	2005
All-Purpose Bike Stand	$0.00	$0.00	$18,921.00	$20,670.00	$15,421.00
AWC Logo Cap	$2,917.57	$10,317.96	$23,202.10	$17,943.39	$0.00
Bike Wash - Dissolver	$0.00	$0.00	$9,922.62	$8,676.15	$6,626.25
Cable Lock	$0.00	$10,119.00	$6,161.40	$0.00	$0.00