tsql - tsql order by with distinct for xml 路径

Question

我有一个查询，稍后我会使用它来进行动态数据透视。我想在执行不同的操作之前订购此查询。我怎么能做到这一点？每当我订购它时，它都会完全打破独特性。

    DECLARE @cols NVARCHAR(max);
SET @cols = STUFF
(
    (SELECT distinct ',' + c.[PivotColumnTitle]
        FROM [myTable] c
        WHERE c.Id = @idInput
        AND c.IsPivotVisible = 1
              --order by c.PivotColumnOrder
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,''
)

标签： tsqlstored-procedurespivotsql-order-bydistinct

Answer 1

尝试这个：

DECLARE @cols NVARCHAR(max);

SET @cols = STUFF
(
    (
        SELECT ',' + c.[PivotColumnTitle]
        FROM [myTable] c
        WHERE c.Id = @idInput
            AND c.IsPivotVisible = 1
        GROUP BY c.[PivotColumnTitle]
        order by MAX(c.PivotColumnOrder)
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,''
)