SQL 2005中的PIVOT

Question

我需要旋转一列（“数字”列）。示例需要此数据：a 1a 2b 3b 4c 5d 6d 7d 8d 9e 10e 11e 12e 13e 14看起来像这样a 1 2b 3 4c 5d 6 7 8 9e 10 11 12 13 14任何帮助将不胜感激...

Answer 1

使用ROW_NUMBER()，PIVOT以及一些动态SQL（但没有必要光标）：

CREATE TABLE [dbo].[stackoverflow_198716](

    [code] [varchar](1) NOT NULL,

    [number] [int] NOT NULL

) ON [PRIMARY]

DECLARE @sql AS varchar(max)

DECLARE @pivot_list AS varchar(max) -- Leave NULL for COALESCE technique

DECLARE @select_list AS varchar(max) -- Leave NULL for COALESCE technique

SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + ']'

        ,@select_list = COALESCE(@select_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + '] AS [col_' + CONVERT(varchar, PIVOT_CODE) + ']'

FROM (

    SELECT DISTINCT PIVOT_CODE

    FROM (

        SELECT code, number, ROW_NUMBER() OVER (PARTITION BY code ORDER BY number) AS PIVOT_CODE

        FROM stackoverflow_198716

    ) AS rows

) AS PIVOT_CODES

SET @sql = '

;WITH p AS (

    SELECT code, number, ROW_NUMBER() OVER (PARTITION BY code ORDER BY number) AS PIVOT_CODE

    FROM stackoverflow_198716

)

SELECT code, ' + @select_list + '

FROM p

PIVOT (

    MIN(number)

    FOR PIVOT_CODE IN (

        ' + @pivot_list + '

    )

) AS pvt

'

PRINT @sql

EXEC (@sql)

Answer 2

SSRS中的Matrix控件具有动态列，如果该数据仍然绑定到报表，则可以使用它。否则，您将必须创建一个sql sproc，该sql sproc像动态示例一样动态生成sql，然后执行它。