Oracle行转列与列转行

Cavlier

2015-11-02

一、行转列

在有些应用场景中，如学生成绩。学生所有科目的成绩都保存到一张表里面，当我们需要以列表的形式显示出学生所对应的每一科目的成绩的时候，需要使用到行转列。

示例

1 -- 学生成绩表
 2 create table grade (
 3     id varchar2(64) not null,
 4     name varchar2(20) not null,
 5     course varchar2(100) not null,
 6   score number(3) not null,
 7     primary key (id)
 8 ) 
 9 
10 -- 初始化数据
11 insert into grade values( '100001', '张三', '语文', 75);
12 insert into grade values( '100002', '张三', '数学', 80);
13 insert into grade values( '100003', '张三', '英语', 80);
14 
15 insert into grade values( '100004', '李四', '语文', 86);
16 insert into grade values( '100005', '李四', '数学', 66);
17 insert into grade values( '100006', '李四', '英语', 81);
18 
19 insert into grade values( '100007', '王五', '语文', 92);
20 insert into grade values( '100008', '王五', '数学', 78);
21 insert into grade values( '100009', '王五', '英语', 82);
22 
23 insert into grade values( '100010', '赵六', '语文', 80);
24 insert into grade values( '100011', '赵六', '数学', 90);
25 insert into grade values( '100012', '赵六', '英语', 77);
26 
27 insert into grade values( '100013', '李明', '语文', 69);
28 insert into grade values( '100014', '李明', '数学', 75);
29 insert into grade values( '100015', '李明', '英语', 80);

行转列语句

1 select
2 t.name "姓名",
3 sum(decode(t.course, '语文', t.score , 0)) "语文",
4 sum(decode(t.course, '数学', t.score , 0)) "数学", 
5 sum(decode(t.course, '英语', t.score , 0)) "英语"   
6 from grade t 
7 group by t.name

因为这里使用到了 group by t.name，所以对于要展示score只能使用聚合函数(sum,max,min)都可以，如果不使用聚合函数就会报错。

执行结果如下：

Oracle行转列与列转行

二、列转行

与行转列相反，有些场景刚好需要这种操作。

示例如下：

1 --列转行
 2 create table sc_grade (
 3     id varchar2(64) not null,
 4     name varchar2(20) not null,
 5     cn_score number(3) not null,
 6     math_score number(3) not null,
 7   en_score number(3) not null,
 8     primary key (id)
 9 )
10  
11 --初始数据
12 insert into sc_grade values ('10001', '张三', 75, 80, 80);
13 insert into sc_grade values ('10002','王五', 92, 78, 82);
14 insert into sc_grade values ('10003','赵六', 80, 90, 77);
15 insert into sc_grade values ('10004','李四', 86, 66, 81);
16 insert into sc_grade values ('10005','李明', 69, 75, 80);

　列传行语句

1 select name "姓名" , '语文' course, cn_score as score from sc_grade
2 union 
3 select name "姓名" , '数学' course, math_score as score from sc_grade
4 union 
5 select name "姓名" , '英语' course, en_score as score from sc_grade
6 order by "姓名" , course

结果如下：

Oracle行转列与列转行

也可以使用insert all .. into完成。使用insert all ..。 into 需要新建一个表用来保存转换后的数据。

表结构如下：

1 -- 结果存放表
2 create table test_sc_grade (  
3     name varchar2(20) not null,   
4     course varchar2(100) not null,   
5     score     number(3) not null 
6 )

执行语句如下：

1 insert all 
2 into test_sc_grade(name,course,score) values(name, '语文', cn_score) 
3 into test_sc_grade(name,course,score) values(name, '数学', math_score) 
4 into test_sc_grade(name,course,score) values(name, '英语', en_score) 
5 select name, cn_score, math_score, en_score from sc_grade; 
6 commit; 
7 select * from test_sc_grade

结果查询与上面截图一致。

oracle行转列