SQL Server PIVOT with multiple X-axis columns
- by HeavenCore
Take the following example data:
Payroll Forname Surname Month Year Amount
0000001 James Bond 3 2011 144.00
0000001 James Bond 6 2012 672.00
0000001 James Bond 7 2012 240.00
0000001 James Bond 8 2012 1744.50
0000002 Elvis Presley 3 2011 1491.00
0000002 Elvis Presley 6 2012 189.00
0000002 Elvis Presley 7 2012 1816.50
0000002 Elvis Presley 8 2012 1383.00
How would i PIVOT this on the Year + Month (eg: 201210) but preserve Payroll, Forename & Surname as seperate columns, for example, the above would become:
Payroll Forename Surname 201103 201206 201207 201208
0000001 James Bond 144.00 672.00 240.00 1744.50
0000002 Elvis Presley 1491.00 189.00 1816.50 1383.00
I'm assuming that because the Year + Month names can change then i will need to employ dynamic SQL + PIVOT - i had a go but couldnt even get the code to parse, nevermind run - any help would be most appreciated!
Edit: What i have so far:
INSERT INTO #tbl_RawDateBuffer
( PayrollNumber ,
Surname ,
Forename ,
[Month] ,
[Year] ,
AmountPayable
)
SELECT PayrollNumber ,
Surname ,
Forename ,
[Month] ,
[Year] ,
AmountPayable
FROM RawData
WHERE [Max] > 1500
DECLARE @Columns AS NVARCHAR(MAX)
DECLARE @StrSQL AS NVARCHAR(MAX)
SET @Columns = STUFF((SELECT DISTINCT
',' + QUOTENAME(CONVERT(VARCHAR(4), c.[Year]) + RIGHT('00' + CONVERT(VARCHAR(2), c.[Month]), 2))
FROM #tbl_RawDateBuffer c
FOR XML PATH('') ,
TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SET @StrSQL = 'SELECT PayrollNumber, ' + @Columns + ' from
(
select PayrollNumber
, CONVERT(VARCHAR(4), [Year]) + RIGHT(''00'' + CONVERT(VARCHAR(2), [Month]), 2) dt
from #tbl_RawDateBuffer
) x
pivot
(
sum(AmountPayable)
for dt in (' + @Columns + ')
) p '
EXECUTE(@StrSQL)
DROP TABLE #tbl_RawDateBuffer
