SQLServer行转列实现思路记录

最近面试遇到了一道面试题，顿时有点迷糊，只说出了思路，后来百度了一下，整理了一下思路，于是记录下来，方便以后学习。（面试题请参见附件）

相关的数据表：

1.score表
 
2.[user]表
 
sql语句如下：

--方法一：静态sql

select * from
(select uid,name, score,scorename from score,[user] where score.uid=[user].id) as sourcetable
pivot(avg(score)for scorename in ([英语], [数学])) as a

--方法二：动态sql

declare @s nvarchar(4000)
select @s = isnull(@s + ',', '') + quotename(scorename)
from (select distinct scorename from score) as a ---列名不要重复

declare @sql nvarchar(4000)
set @sql='
select r.* from
(select uid,name,scorename,score from score,[user] where score.uid=[user].id) as t
pivot
(
max(t.score)
for t.scorename in ('+@s+')
) as r'
exec( @sql)

--方法三：case when

select
row_number() over(order by [user].id) as 编号,
uid as 用户编号,
name as 姓名,
max(case scorename when '英语' then score else 0 end) 英语,
max(case scorename when '数学' then score else 0 end) 数学
from score,[user] where score.uid=[user].id
group by uid,[user].id,name