IT忍者神龟之 oracle行转列、列转行
时间:2021-07-01 10:21:17
帮助过:3人阅读
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语句为:
[sql]
view plaincopy
- 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
[sql]
view plaincopy
- 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语句为:
[sql]
view plaincopy
- 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
IT忍者神龟之 oracle行转列、列转行
标签: