I have had to produce crosstab/pivot queries from SQL Server 2000 before, and it ain't pretty. Typically the data is stored in rows, and the data in one field needs to be turned into a column header. To cut straight to the chase, here's an article and stored procedure on SearchSQLServer.com that will do exactly that: A simple way to perform crosstab operations By Brian Walker
Brilliant! One note: you don't have to put this stored proc in master, it can go in any database.
Keep reading to find my slower, non-dynamic SQL way to do it. Here's some psuedo-code for when you know the columns that will be produced (an example is months of the year, although in this fake code I'm only producing two "pivot" columns):
--this is fake code, it does not work!
SELECT BaseColumn, SUM(X), SUM(Y)
FROM (
SELECT BaseColumn,
IF ColumnHeader = x THEN FigureToBePivoted AS X,
IF ColumnHeader = y THEN FigureToBePivoted AS Y
FROM (
SELECT BaseColumn, ColumnHeader, FigureToBePivoted
)
)
GROUP BY BaseColumn
And here's an example that works in Northwind, with months of the year across the top:
--the outer-most query SUMs the OrderTotal for each month and groups by the ProductName
--so that a product appears once, with all its OrderTotal dollar values in month columns.
--Note there will be NULLs returned where a ProductName had no orders in a month
SELECT X.ProductName,
SUM(X.[01_Raw]) AS [Jan], SUM(X.[02_Raw]) AS [Feb], SUM(X.[03_Raw]) AS [Mar],
SUM(X.[04_Raw]) AS [Apr], SUM(X.[05_Raw]) AS [May], SUM(X.[06_Raw]) AS [Jun],
SUM(X.[07_Raw]) AS [Jul], SUM(X.[08_Raw]) AS [Aug], SUM(X.[09_Raw]) AS [Sep],
SUM(X.[10_Raw]) AS [Oct], SUM(X.[11_Raw]) AS [Nov], SUM(X.[12_Raw]) AS [Dec]
FROM (
--this query splits each OrderMonth to its own column. However after the result of this we
--end up with staggered results where no two months appear on the same line, so we need to
--SUM the results at a later step
SELECT I.ProductName,
CASE WHEN I.OrderMonth = 1 THEN I.OrderTotal ELSE NULL END AS [01_Raw],
CASE WHEN I.OrderMonth = 2 THEN I.OrderTotal ELSE NULL END AS [02_Raw],
CASE WHEN I.OrderMonth = 3 THEN I.OrderTotal ELSE NULL END AS [03_Raw],
CASE WHEN I.OrderMonth = 4 THEN I.OrderTotal ELSE NULL END AS [04_Raw],
CASE WHEN I.OrderMonth = 5 THEN I.OrderTotal ELSE NULL END AS [05_Raw],
CASE WHEN I.OrderMonth = 6 THEN I.OrderTotal ELSE NULL END AS [06_Raw],
CASE WHEN I.OrderMonth = 7 THEN I.OrderTotal ELSE NULL END AS [07_Raw],
CASE WHEN I.OrderMonth = 8 THEN I.OrderTotal ELSE NULL END AS [08_Raw],
CASE WHEN I.OrderMonth = 9 THEN I.OrderTotal ELSE NULL END AS [09_Raw],
CASE WHEN I.OrderMonth = 10 THEN I.OrderTotal ELSE NULL END AS [10_Raw],
CASE WHEN I.OrderMonth = 11 THEN I.OrderTotal ELSE NULL END AS [11_Raw],
CASE WHEN I.OrderMonth = 12 THEN I.OrderTotal ELSE NULL END AS [12_Raw]
FROM (
--this inner query returns the column(s) we want to return results for (ProductName),
--the data (OrderTotal), and the column headings (OrderMonth)
SELECT Products.ProductName,
MONTH(Orders.OrderDate) AS OrderMonth,
[Order Details].UnitPrice * [Order Details].Quantity AS OrderTotal
FROM dbo.[Order Details] INNER JOIN
dbo.Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN
dbo.Products ON [Order Details].ProductID = Products.ProductID
WHERE YEAR(Orders.OrderDate) = 1997
) I
) X
GROUP BY X.ProductName
ORDER BY X.ProductName
You can see that this is a lot of code to be copy-and-pasted, which allows much room for error. It follows my approach of basic, blunt, step-by-step SQL. One advantage of this method (there has to be one good thing, right?) is that you can change the inner-most query to return a different figure, and as long as it's aliased to (in this case) "OrderTotal" you'll still get valid results from the outer queries...however, if you want to add an extra column, you need to propogate that all the way from the inner-most to the outer-most query. I find that if I need extra reference columns, I put the results from the pivot into an @temp table variable and join them later, rather than having a lot of non-pivot columns being mucked around with inside the multi-level select.
There are other alternatives, most of which I've considered and had to discard: Excel Pivot Tables, Analysis Services, and Reporting Services Matrix control.