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:

Post a Comment