sql - 如何按升序排列 SQL 数据透视表的列

Question

有人可以帮我按升序排列列吗？

我的数据透视表的输出如下:

Ref             role_name   offer_id    10000   104000  8000    8400
43132_43282     Call Center 1            1        0      0       6
43132_43282     Others      1            2        0      0       3

相反，我希望它是这样的:

Ref             role_name   offer_id    8000    8400    10000   104000
43132_43282     Call Center     1        0        6       1       0
43132_43282     Others          1        0        3       2       0


DECLARE @cols AS NVARCHAR(MAX), -- for pivot
    @cols2 AS NVARCHAR(MAX), -- for select
    @query AS NVARCHAR(MAX);

SET @cols = STUFF((
            SELECT DISTINCT ',' + QUOTENAME(c.[Offer_cover])
            FROM #cover2 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
-- this is for the SELECT
SET @cols2 = STUFF((
            SELECT DISTINCT ',' + 'ISNULL(' + QUOTENAME(c.[Offer_cover]) + ', 0) ' + QUOTENAME(c.[Offer_cover])
            FROM #cover2 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SET @query = 'SELECT Ref,role_name,offer_id, ' + @cols2 + ' from 
            (
                select *
                from #cover2 
            ) x 
            pivot 
            (
                 SUM(cover_earning_Count)
                 for [Offer_cover] in (' + @cols + ') 
            ) p'

EXECUTE (@query)

Answer 1

您能否在 @cols2 变量选择中添加 GROUP BY, ORDER BY [Offer_cover] 并删除 DISTINCT。

所以查询将是:

SET @cols2 = STUFF((
            SELECT ',' + 'ISNULL(' + QUOTENAME(c.[Offer_cover]) + ', 0) ' + QUOTENAME(c.[Offer_cover])
            FROM #cover2 c
            GROUP BY c.[Offer_cover]  -- changes here
            ORDER BY c.[Offer_cover]  -- changes here
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

它会按升序返回@cols2，因此会影响最终结果。