SQL Server 行转列 学习笔记

学习参考: https://blog.csdn.net/pan_junbiao/article/details/80957274

数据准备:

1. T_B_Student表

2. T_B_Score表

select pvt.SNO as 学号,pvt.NAME as 姓名,pvt.语文,pvt.数学,pvt.英语,pvt.历史,pvt.地理,pvt.生物,pvt.政治,pvt.物理,pvt.化学,pvt.合计 from (
	select A.SNO,B.NAME,A.CNAME,A.ISCORE from T_B_Score A left join T_B_Student B on A.SNO=B.NO
	union all
	--select Distinct(SNO) as SNO,'' as NAME,'合计' as CNAME,SUM(ISCORE) as ISCORE from T_B_Score group by SNO
	select Distinct(A.SNO) as SNO,B.NAME,'合计' as CNAME,SUM(ISCORE) as ISCORE from T_B_Score A left join T_B_Student B on A.SNO=B.NO group by SNO,NAME
) p PIVOT(
	SUM([ISCORE]) for [CNAME] in([语文],[数学],[英语],[历史],[地理],[生物],[政治],[物理],[化学],[合计])
) as pvt
order by pvt.SNO

select SNO,NAME,
	SUM(case [CNAME] when '语文' then IScore else 0 end) as '语文',
	SUM(case [CNAME] when '数学' then IScore else 0 end) as '数学',
	SUM(case [CNAME] when '英语' then IScore else 0 end) as '英语',
	SUM(case [CNAME] when '历史' then IScore else 0 end) as '历史',
	SUM(case [CNAME] when '地理' then IScore else 0 end) as '地理',
	SUM(case [CNAME] when '生物' then IScore else 0 end) as '生物',
	SUM(case [CNAME] when '政治' then IScore else 0 end) as '政治',
	SUM(case [CNAME] when '物理' then IScore else 0 end) as '物理',
	SUM(case [CNAME] when '化学' then IScore else 0 end) as '化学',
	SUM(ISCORE) as '合计'
from (
	select A.SNO,B.NAME,A.CNAME,A.ISCORE from T_B_Score A right join T_B_Student B on A.SNO=B.NO
) s group by SNO,NAME