日期:2014-05-17 浏览次数:20471 次
declare @s nvarchar(4000) Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号 exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a pivot (max([Score]) for [Course] in('+@s+'))b ')
--> 测试数据:[tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([PERA] VARCHAR(1),[PERB] VARCHAR(1),[D] INT,[S] INT) INSERT [tb] SELECT 'A','B',3,2 UNION ALL SELECT 'A','C',5,4 UNION ALL SELECT 'A','D',4,2 --------------开始查询-------------------------- DECLARE @s VARCHAR(MAX) SET @s = '' SELECT @s = @s + ',[' + LTRIM([PERB]) + '_D]=max(case when [PERB]=' + QUOTENAME([PERB], '''') + ' then [D] else 0 end)' FROM [tb] GROUP BY [PERB] SELECT @s = @s + ',[' + LTRIM([PERB]) + '_S]=max(case when [PERB]=' + QUOTENAME([PERB], '''') + ' then [S] else 0 end)' FROM [tb] GROUP BY [PERB] SET @s = 'select [PERA]' + @s + ' from [tb] group by [PERA]' EXEC(@s) /* PERA B_D C_D D_D B_S C_S D_S ---- ----------- ----------- ----------- ----------- ----------- ----------- A 3 5 4 2 4 2 (1 行受影响) a */