T-SQL行列互转(PIVOT/UNPIVOT)

--检查临时表是否存在
if OBJECT_ID('#tb') is not null
drop table #tb
go

--创建临时表
create table #tb
(
	name nvarchar(20),--姓名
	course nvarchar(20),--课程
	score int--分数
)
go

--临时数据
insert #tb 
select '张三','语文',72 union all
select '张三','数学',83 union all
select '张三','英语',94 union all
select '李四','语文',74 union all
select '李四','数学',84 union all
select '李四','英语',95
go

--原始方案
select 
name as 姓名,
max(case course when '语文' then score else 0 end) 语文,
max(case course when '数学' then score else 0 end) 数学,
max(case course when '英语' then score else 0 end) 英语
from #tb 
group by name;

--高级方案PIVOT
select 
name 姓名,
语文,
数学,
英语
from #tb
pivot (max(score) for course in (语文,数学,英语)) pv;

--临时数据
select * into #tb2 from 
(select 
name,
语文,
数学,
英语
from #tb
pivot (max(score) for course in (语文,数学,英语)) pv) t;

--UNPIVOT
select 
name as 姓名,
course as 课程,
score as 分数
from #tb2
unpivot (score for course in ([语文],[数学],[英语])) upv;