纵表、横表互转的SQL

纵表、横表互转的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]

posted @ 2013-09-22 21:52  大志若愚  阅读(22448)  评论(2编辑  收藏  举报