Twitter Updates

    follow me on Twitter

    Thursday, August 03, 2006

    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 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



    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

    No comments: