具有多个X轴列的SQL Server PIVOT [英] SQL Server PIVOT with multiple X-axis columns

问题描述

获取以下示例数据:

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

我如何在年份和月份(例如:201210)上保存此信息，但保留薪资，姓氏和姓名?例如，将姓氏作为单独的列，则将变为:

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

我假设因为Year + Month的名称可以更改，所以我将需要使用动态SQL + PIVOT-我可以尝试，但是甚至无法解析代码，没关系-任何帮助将不胜感激！

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!

到目前为止我所拥有的:

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

推荐答案

好吧，正如您所说的那样，您将需要动态SQL，因此首先请转到

Ok, as you said, you are gonna need dynamic SQL, so first go to this link. Once you read that, try the following:

更新后的代码注释:

DECLARE @cols AS NVARCHAR(MAX), @cols2 AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

WITH CTE AS
(
    SELECT *, CAST([Year] AS NVARCHAR(4))+RIGHT('00'+CAST([Month] AS NVARCHAR(2)),2) YearMonth
    FROM YourTable
)

SELECT @cols = STUFF((  SELECT DISTINCT ',' + QUOTENAME(YearMonth) 
                        FROM CTE 
                        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''),
       @cols2 = STUFF(( SELECT DISTINCT ',ISNULL(' + QUOTENAME(YearMonth) + ',0) AS ' + QUOTENAME(YearMonth)
                        FROM CTE 
                        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')


SET @query = '
SELECT Payroll, Forname, Surname, ' + @cols2 + '
FROM (  SELECT  Payroll, Forname, Surname, 
                CAST([Year] AS NVARCHAR(4))+RIGHT(''00''+CAST([Month] AS NVARCHAR(2)),2) YearMonth,
                Amount
        FROM YourTable ) T
PIVOT(SUM(Amount) FOR YearMonth IN ('+@cols+')) PT'

EXEC(@Query)

这篇关于具有多个X轴列的SQL Server PIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！