那就用动态的咯DECLARE @s NVARCHAR(4000) SET @s = '' SELECT @s = @s + ',' + QUOTENAME([Subject]) + '=sum(case when [Subject]=' + QUOTENAME([Subject], '''') + ' then [Score] else 0 end)' FROM [StudentsScore] GROUP BY [Subject] EXEC('select [Student]'+@s+' from [StudentsScore] group by [Student] order by [Student]')
拼接sql那样的我也知道 我想用PIVOT这个 如果不可以的话 那这个函数也不是很厉害的样子 囧
DECLARE @s NVARCHAR(4000) SELECT @s = ISNULL(@s + ',', '') + QUOTENAME([SUBJECT]) FROM StudentsScore GROUP BY [SUBJECT] EXEC('select * from StudentsScore pivot (sum([Score]) for [Subject] in('+@s+'))b')
SET @s = ''
SELECT @s = @s + ',' + QUOTENAME([Subject]) + '=sum(case when [Subject]='
+ QUOTENAME([Subject], '''') + ' then [Score] else 0 end)'
FROM [StudentsScore]
GROUP BY [Subject]
EXEC('select [Student]'+@s+' from [StudentsScore] group by [Student] order by [Student]')
SELECT @s = ISNULL(@s + ',', '') + QUOTENAME([SUBJECT])
FROM StudentsScore
GROUP BY [SUBJECT]
EXEC('select * from StudentsScore pivot (sum([Score]) for [Subject] in('+@s+'))b')