SQL 2005中的PIVOT

Question

我需要旋转一列（“数字”列）。示例需要此数据：

a 1

a 2

b 3

b 4

c 5

d 6

d 7

d 8

d 9

e 10

e 11

e 12

e 13

e 14

看起来像这样

a 1 2

b 3 4

c 5

d 6 7 8 9

e 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 techniqueDECLARE @select_list AS varchar(max) -- Leave NULL for COALESCE techniqueSELECT @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_CODESSET @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 pPIVOT (    MIN(number)    FOR PIVOT_CODE IN (        ' + @pivot_list + '    )) AS pvt'PRINT @sqlEXEC (@sql)

Answer 2

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