SQL 行列转换

create table score ([user] varchar(20),cause nvarchar(20),value int)insert score values ('luzhenhua','语文',80)insert score values ('luzhenhua','数学',90)insert score values ('liuzw','数学',30)insert score values ('liuzw','语文',80)insert score values ('liuzw','英语',50)--使用case语句实现select [user],sum(case cause when '语文' then value end) as 语文,sum(case cause when '数学' then value end) as 语文,sum(case cause when '英语' then value else 0 end) as 英语from scoregroup by [user]--使用pivot实现declare @columnName varchar(100);set @columnName = '[语文],[数学],[英语]'declare @sql nvarchar(4000)set @sql = N'select [user],'+@columnName+'from (select [user],cause,value from score) as spivot (sum(value)for cause in ('+@columnName+')) as p'exec (@sql)