IT忍者神龟之 oracle行转列、列转行，忍者神龟o

IT忍者神龟之 oracle行转列、列转行，忍者神龟oracle

一、行转列

需要将如下格式

转换为：

 

这就是最常见的行转列，主要原理是利用decode函数、聚集函数(sum)，结合group by分组实现的

 

[sql] view plaincopy

1. create table test(  
2.        id varchar2(255) primary key not null,  
3.        name varchar2(255),  
4.        course varchar2(255),  
5.        score varchar2(255)  
6. );  
7. insert into test values(sys_guid(),'zhangsan','语文',85);  
8. insert into test values(sys_guid(),'zhangsan','数学',78);  
9. insert into test values(sys_guid(),'zhangsan','英语',90);  
10. insert into test values(sys_guid(),'lisi','语文',73);  
11. insert into test values(sys_guid(),'lisi','数学',84);  
12. insert into test values(sys_guid(),'lisi','英语',92);  

行转列SQL语句为：

[sql] view plaincopy

1. select t.name,   
2.   sum(decode(t.course, '语文', score,null)) as chinese,   
3.   sum(decode(t.course, '数学', score,null)) as math,   
4.   sum(decode(t.course, '英语', score,null)) as english   
5. from test t   
6. group by t.name   
7. order by t.name   

二、列转行

将如下格式

转换为

 

这就是最常见的列转行，主要原理是利用SQL里面的union

 

[sql] view plaincopy

1. create table test(  
2.        id varchar2(255) primary key not null,  
3.        name varchar2(255),  
4.        ch_score   varchar2(255),  
5.        math_score varchar2(255),  
6.        en_score   varchar2(255)  
7. );  
8.   
9. insert into test values(sys_guid(),'zhangsan',88,76,90);  
10. insert into test values(sys_guid(),'lisi',91,67,82);  

列转行SQL语句为：

[sql] view plaincopy

1. select name, '语文' COURSE , ch_score as SCORE from test    
2. union select name, '数学' COURSE, MATH_SCORE as SCORE from test    
3. union select name, '英语' COURSE, EN_SCORE as SCORE from test    
4. order by name,COURSE  
   

oracle下可以用函数decode处理:

select 产品名称,
sum(decode(季度,'第一季度',销售额,0)) 第一季度销售额,
sum(decode(季度,'第二季度',销售额,0)) 第二季度销售额,
sum(decode(季度,'第三季度',销售额,0)) 第三季度销售额,
sum(decode(季度,'第四季度',销售额,0)) 第四季度销售额,
from 表名
group by 产品名称;
 

select id, substr( names, instr('||'|| names, '||', 1, rn),
instr( names||'||', '||', 1, rn) - instr('||'|| names, '||', 1, rn) ) name
from A,(select rownum rn from dual connect by rownum < 10)
where instr('||'|| names, '||', 1, rn) > 0