IT忍者神龜之oracle行轉列、列轉行
一、行轉列
需要將如下格式
轉換為：
這就是最常見的行轉列，主要原理是利用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
