笔试 -- SQL语句之横竖表转换

最近笔试中有 【将竖表转换为横表】的题 ，记录一下横竖表转换的SQL语句~

一、表结构

竖表结构

create table Table_A
(
    姓名 varchar(20),
    课程 varchar(20),
    成绩 int
)
insert into Table_A(姓名,课程,成绩) values('张三','语文',60)
insert into Table_A(姓名,课程,成绩) values('张三','数学',70)
insert into Table_A(姓名,课程,成绩) values('张三','英语',80)
insert into Table_A(姓名,课程,成绩) values('李四','语文',90)
insert into Table_A(姓名,课程,成绩) values('李四','数学',100)

横表结构

create table Table_B
(
    姓名 varchar(20),
    语文 int,
    数学 int,
    英语 int
)
insert into Table_B(姓名,语文,数学,英语) values('张三',60,70,80)
insert into Table_B(姓名,语文,数学,英语) values('李四',90,100,0)

二、竖表转横表

Table_A --> Table_B

1-1 case when then

select
[姓名],
sum(case [课程] when '语文' then [成绩]  end) as [语文],
sum(case [课程] when '数学' then [成绩]  end) as [数学],
sum(case [课程] when '英语' then [成绩]  end) as [英语]
from [Table_A]
group by [姓名]

1-2 pivot

select * from [Table_A]
pivot(
	max([成绩])for [课程]in ([语文],[数学],[英语])
) as 临时表

执行结果

三、横表转竖表

Table_B --> Table_A

1-1 UNION ALL

select [姓名],'语文'as 课程,语文 as [成绩] from [Table_B] union all
select [姓名],'数学'as 课程,数学 as [成绩] from [Table_B] union all
select [姓名],'英语'as 课程,英语 as [成绩] from [Table_B]
order by [姓名],[课程]

1-2 unpivot

select [姓名],[课程],[成绩] from [Table_B]
unpivot
(
	[成绩] for [课程] in([语文],[数学],[英语])
) as 临时表
order by [姓名],[课程]

执行结果：