需要将如下格式
转换为：
这就是最常见的行转列，主要原理是利用decode函数、聚集函数(sum)，结合group by分组实现的
[sql] view plaincopy
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] 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] 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] 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