sql-server - 在数据透视查询的填充部分中排序

Question

我想使用数据透视查询的内容部分按升序对日期进行排序。

示例:

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

select @cols = STUFF((SELECT  distinct ',' + QUOTENAME(column_date)--(column_date)
              FROM #temp 
              order by column_date /* Error occured here*/
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
PRINT(@cols)

在 order by 子句附近获取上述脚本的错误。

错误详情:

Msg 145, Level 15, State 1, Line 4  
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Answer 1

试试这个:

您可以使用 Group by column_date 它将返回不同的日期

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

select @cols = STUFF((SELECT    ',' + QUOTENAME(column_date)--(column_date)
              FROM #temp 
              GROUP BY column_date
              order by column_date /* Error occured here*/
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
PRINT(@cols)