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 score
group 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 s
pivot (
sum(value)
for cause in ('+@columnName+')
) as p'
exec (@sql)