为动态数据透视表排序列

Question

我有以下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,'')

我曾尝试弄乱本主题中发布的答案，但无法运行查询。我希望有人能提供帮助。

编辑 我只是注意到我的行是乱序的，也想按[客户ID]进行排序。

Answer 1

您可以通过在ORDER BY设置@cols字符串时添加来调整动态数据透视查询中字段的顺序：

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,'')