一、行转列
须要将例如以下格式
转换为：
这就是最常见的行转列，主要原理是利用decode函数、聚集函数(sum)。结合group by分组实现的
- create table test(
- id varchar2(255) primary key not null,
- name varchar2(255),
- course varchar2(255),
- score varchar2(255)
- );
- insert into test values(sys_guid(),‘zhangsan‘,‘语文‘,85);
- insert into test values(sys_guid(),‘zhangsan‘,‘数学‘,78);
- insert into test values(sys_guid(),‘zhangsan‘,‘英语‘,90);
- insert into test values(sys_guid(),‘lisi‘,‘语文‘,73);
- insert into test values(sys_guid(),‘lisi‘,‘数学‘,84);
- insert into test values(sys_guid(),‘lisi‘,‘英语‘,92);
行转列SQL语句为：
- select t.name,
- sum(decode(t.course, ‘语文‘, score,null)) as chinese,
- sum(decode(t.course, ‘数学‘, score,null)) as math,
- sum(decode(t.course, ‘英语‘, score,null)) as english
- from test t
- group by t.name
- order by t.name
二、列转行
将例如以下格式
转换为
这就是最常见的列转行。主要原理是利用SQL里面的union
- create table test(
- id varchar2(255) primary key not null,
- name varchar2(255),
- ch_score varchar2(255),
- math_score varchar2(255),
- en_score varchar2(255)
- );
-
- insert into test values(sys_guid(),‘zhangsan‘,88,76,90);
- insert into test values(sys_guid(),‘lisi‘,91,67,82);
列转行SQL语句为：
- select name, ‘语文‘ COURSE , ch_score as SCORE from test
- union select name, ‘数学‘ COURSE, MATH_SCORE as SCORE from test
- union select name, ‘英语‘ COURSE, EN_SCORE as SCORE from test
- order by name,COURSE
