oracle行转列、列转行

一、行转列

需要将如下格式

转换为：

 

这就是最常见的行转列，主要原理是利用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