纵表、横表互转的SQL
By:大志若愚
1、建表:
纵表结构 Table_A
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)
姓名 |
课程 |
成绩 |
张三 |
语文 |
60 |
张三 |
数学 |
70 |
张三 |
英语 |
80 |
李四 |
语文 |
90 |
李四 |
数学 |
100 |
横表结构 Table_B
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)
姓名 |
语文 |
数学 |
英语 |
张三 |
60 |
70 |
80 |
李四 |
90 |
100 |
0 |
2、纵表变横表
纵表结构 Table_A --> 横表结构 Table_B
方法一:聚合函数[max或sum]配合case语句
select 姓名,
sum (case 课程 when '语文' then 成绩 else 0 end) as 语文,
sum (case 课程 when '数学' then 成绩 else 0 end) as 数学,
sum (case 课程 when '英语' then 成绩 else 0 end) as 英语
from Table_A
group by 姓名
方法二:使用pivot
select * from Table_A pivot (max(成绩)for 课程 in(语文,数学,英语)) 临时表
3、横表变纵表
横表结构 Table_B --> 纵表结构 Table_A
方法一: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 姓名,课程 desc
方法二:使用unpivot
select 姓名,课程,成绩 from Table_B
unpivot
(成绩 for 课程 in ([语文],[数学],英语)) 临时表
说明:在实际开发中表名,列名不应该使用汉字,在插入的值中有汉字的应该用N修饰,以防止出现乱码,出现意想不到的结果,可能产生2异性的表名可以用[]修饰。
例如:
insert into Table_B(name,chinese,math,english) values(N'张三',60,70,80)
create table [user]