oracle行转列与列转行

简介: 一、行转列   在有些应用场景中,如学生成绩。学生所有科目的成绩都保存到一张表里面,当我们需要以列表的形式显示出学生所对应的每一科目的成绩的时候,需要使用到行转列。   示例 1 -- 学生成绩表 2 create table grade ( 3 id varchar2(...

一、行转列

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

  示例

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

  执行结果如下:

  

二、列转行

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

  示例如下: 

 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 

  结果如下:

  

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

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

目录
相关文章
|
7月前
|
Oracle 关系型数据库
Oracle查询优化-行转列
【2月更文挑战第6天】【2月更文挑战第15篇】行转列
60 4
|
7月前
|
Oracle 关系型数据库
Oracle查询优化-列转行
【2月更文挑战第6天】【2月更文挑战第16篇】列转行
66 4
|
7月前
|
SQL Oracle 关系型数据库
Oracle数据库之PIVOT实现行转列
Oracle数据库之PIVOT实现行转列
204 0
|
Oracle 关系型数据库 MySQL
oracle 行转列函数 wm_concat(列)
oracle 行转列函数 wm_concat(列)
635 0
|
存储 Oracle 关系型数据库
Oracle行转列函数PRINT_TABLE的用法
Oracle行转列函数PRINT_TABLE的用法
141 0
|
Oracle 关系型数据库
oracle中的行转列函数
转自:https://blog.csdn.net/qq_33157666/article/details/72854801 一、简单介绍 最近项目需要进行行转列,经过上网查资料发现了wmsys.wm_concat和LISTAGG函数,在这分享给大家 wmsys.wm_concat是oracle 10g推出的,用来连接字符串,LISTAGG是oracle 11g推出的,它的作用和wmsys.wm_concat是一样的,但是他不支持LISTAGG。
3760 0
|
Web App开发 Oracle 关系型数据库
|
2月前
|
存储 Oracle 关系型数据库
Oracle数据库的应用场景有哪些?
【10月更文挑战第15天】Oracle数据库的应用场景有哪些?
192 64