具有多个 X 轴列的 SQL Server PIVOT

2022-01-22 00:00:00 pivot sql tsql sql-server-2008 sql-server

以下面的数据为例:

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)上进行 PIVOT，但保留工资单、姓名和姓氏作为单独的列，例如，上面将变为:

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

我假设因为年份 + 月份名称可以更改，所以我将需要使用动态 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)

相关文章