Crosstab Queries in SQL Server 2000
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
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 YFROM (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 monthSELECT 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 stepSELECT 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 OrderTotalFROM dbo.[Order Details] INNER JOINdbo.Orders ON [Order Details].OrderID = Orders.OrderID INNER JOINdbo.Products ON [Order Details].ProductID = Products.ProductIDWHERE YEAR(Orders.OrderDate) = 1997) I) XGROUP BY X.ProductNameORDER BY X.ProductName
And here’s another that works in Access Control (I am not sure how many of you might have had access to the ACS Lenel OnGuard based system), with days of the month across the top:
SELECT B.BaseColumn as EmployeeName,MAX([01]) as [01], MAX([02]) as [02], MAX([03]) as [03], MAX([04]) as [04], MAX([05]) as [05], MAX([06]) as [06], MAX([07]) as [07],MAX([08]) as [08], MAX([09]) as [09], MAX([10]) as [10], MAX([11]) as [11], MAX([12]) as [12], MAX([13]) as [13], MAX([14]) as [14],MAX([15]) as [15], MAX([16]) as [16], MAX([17]) as [17], MAX([18]) as [18], MAX([19]) as [19], MAX([20]) as [20], MAX([21]) as [21],MAX([22]) as [22], MAX([23]) as [24], MAX([24]) as [24], MAX([25]) as [25], MAX([26]) as [26], MAX([27]) as [27], MAX([28]) as [28],MAX([29]) as [29], MAX([30]) as [30] ,MAX([31]) as [31]FROM (SELECT A.BaseColumn,CASE WHEN A.ColumnHeader = '01' THEN A.FigureToBePivoted END AS [01],CASE WHEN A.ColumnHeader = '02' THEN A.FigureToBePivoted END AS [02],CASE WHEN A.ColumnHeader = '03' THEN A.FigureToBePivoted END AS [03],CASE WHEN A.ColumnHeader = '04' THEN A.FigureToBePivoted END AS [04],CASE WHEN A.ColumnHeader = '05' THEN A.FigureToBePivoted END AS [05],CASE WHEN A.ColumnHeader = '06' THEN A.FigureToBePivoted END AS [06],CASE WHEN A.ColumnHeader = '07' THEN A.FigureToBePivoted END AS [07],CASE WHEN A.ColumnHeader = '08' THEN A.FigureToBePivoted END AS [08],CASE WHEN A.ColumnHeader = '09' THEN A.FigureToBePivoted END AS [09],CASE WHEN A.ColumnHeader = '10' THEN A.FigureToBePivoted END AS [10],CASE WHEN A.ColumnHeader = '11' THEN A.FigureToBePivoted END AS [11],CASE WHEN A.ColumnHeader = '12' THEN A.FigureToBePivoted END AS [12],CASE WHEN A.ColumnHeader = '13' THEN A.FigureToBePivoted END AS [13],CASE WHEN A.ColumnHeader = '14' THEN A.FigureToBePivoted END AS [14],CASE WHEN A.ColumnHeader = '15' THEN A.FigureToBePivoted END AS [15],CASE WHEN A.ColumnHeader = '16' THEN A.FigureToBePivoted END AS [16],CASE WHEN A.ColumnHeader = '17' THEN A.FigureToBePivoted END AS [17],CASE WHEN A.ColumnHeader = '18' THEN A.FigureToBePivoted END AS [18],CASE WHEN A.ColumnHeader = '19' THEN A.FigureToBePivoted END AS [19],CASE WHEN A.ColumnHeader = '20' THEN A.FigureToBePivoted END AS [20],CASE WHEN A.ColumnHeader = '21' THEN A.FigureToBePivoted END AS [21],CASE WHEN A.ColumnHeader = '22' THEN A.FigureToBePivoted END AS [22],CASE WHEN A.ColumnHeader = '23' THEN A.FigureToBePivoted END AS [23],CASE WHEN A.ColumnHeader = '24' THEN A.FigureToBePivoted END AS [24],CASE WHEN A.ColumnHeader = '25' THEN A.FigureToBePivoted END AS [25],CASE WHEN A.ColumnHeader = '26' THEN A.FigureToBePivoted END AS [26],CASE WHEN A.ColumnHeader = '27' THEN A.FigureToBePivoted END AS [27],CASE WHEN A.ColumnHeader = '28' THEN A.FigureToBePivoted END AS [28],CASE WHEN A.ColumnHeader = '29' THEN A.FigureToBePivoted END AS [29],CASE WHEN A.ColumnHeader = '30' THEN A.FigureToBePivoted END AS [30],CASE WHEN A.ColumnHeader = '31' THEN A.FigureToBePivoted END AS [31]FROM (--select empid as BaseColumn, left(convert(varchar,eventime,103),2) as ColumnHeader, 1 as FigureToBePivoted from events where eventime>'2006-06-30 23:59:59.999' and eventime<'2006-08-01 00:00:00.000' SELECT firstname + ' ' + lastname AS BaseColumn, events.ColumnHeader, events.FigureToBePivoted FROM emp LEFT JOIN ( select empid as BaseColumn, LEFT(CONVERT(VARCHAR,eventime,103),2) AS ColumnHeader, 1 AS FigureToBePivoted FROM events WHERE eventime>='2006-07-01 00:00:00.000' AND eventime<='2006-07-31 23:59:59.999' ) events ON emp.[id]=events.BaseColumn WHERE firstname + ' ' + lastname IS NOT NULL AND lastname NOT IN ('Temp Emp','Ground Floor Security','First Floor Security','Housekeeping Supervisor') AND firstname NOT IN ('Temp') ) A ) B GROUP BY B.BaseColumn ORDER BY B.BaseColumn
Reference: http://dotnetjunkies.com/WebLog/thomasswilliams/archive/2005/10/23/133383.aspx