Oracle行转列与列转行

陈晓龙 · 发表于 2020-6-30 23:04:48

一、行转列

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

　　示例

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 select2 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)都可以，如果不使用聚合函数就会报错。

　　执行结果如下：

二、列转行

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

　　示例如下：

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_grade2 union 3 select name "姓名" , '数学' course, math_score as score from sc_grade4 union 5 select name "姓名" , '英语' course, en_score as score from sc_grade6 order by "姓名" , course

　　结果如下：

　　也可以使用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

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