对动态数据透视列进行排序

我有以下 SQL 查询，其中创建的列是无序的，我不太确定如何修复它。

SELECT rhead.rhcust AS [Cust ID], rdetl.rdextp AS [Inv Amt], rhead.rhivdt AS [Inv Date]
INTO #TempTable
FROM rhead
LEFT OUTER JOIN rdetl
    ON rhead.rhinvc = rdetl.rdinvc
WHERE rhead.rhivdt >= '01-01-2012' AND rhead.rhivdt <= '12-25-12'

ALTER DATABASE Vista_TM SET COMPATIBILITY_LEVEL = 100

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Month'+cast(DATEPART(m, [Inv Date]) as varchar(2))) 
                    from #TempTable
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @query = 'SELECT [Cust ID],' + @cols + ' 
            from 
            (
                SELECT [Cust ID], [Inv Amt],
                  ''Month''+cast(DATEPART(m, [Inv Date]) as varchar(2)) MonthNo
                FROM #TempTable
            ) x
            pivot 
            (
                sum([Inv Amt])
                for MonthNo in (' + @cols + ')
            ) p '

execute(@query)

DROP TABLE #TempTable

我相信它必须对查询的这一部分做一些事情：

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Month'+cast(DATEPART(m, [Inv Date]) as varchar(2))) 
                    from #TempTable
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

我确实尝试过修改此中发布的答案topic但我无法运行查询。我希望有人能提供帮助。

Edit我刚刚注意到我的行乱序，并且还想按 [Cust ID] 排序。

您可以通过添加一个来调整动态数据透视查询中字段的顺序ORDER BY当你设置你的@cols string:

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Month'+cast(DATEPART(m, [Inv Date]) as varchar(2))) 
                    from #TempTable
                    ORDER BY ....
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

更新：错过了DISTINCT起初，当使用DISTINCT你必须使用子查询，然后ORDER BY:

SELECT @cols = STUFF((SELECT ',' +   QUOTENAME(ColName) 
                    FROM (SELECT DISTINCT 'Month'+cast(DATEPART(m, [Inv Date]) as varchar(2)) ColName
                          FROM #TempTable
                          )sub
                    ORDER BY ColName
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

如果您不能简单地使用列名，则可能需要在子查询中添加“排序”字段，并且您可以向子查询添加任何字段，只要它们不会破坏子查询DISTINCT列表。例如：

SELECT @cols = STUFF((SELECT ',' +   QUOTENAME(ColName) 
                    FROM (SELECT DISTINCT 'Month'+cast(DATEPART(m, [Inv Date]) as varchar(2)) ColName
                                          ,CASE WHEN field = 'something' THEN 1
                                                WHEN field = 'something else' THEN 2
                                                ELSE 3
                                           END as Sort
                                          ,Cust_ID
                          FROM #TempTable
                          )sub
                    ORDER BY Sort,Cust_ID
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')