Oracle 行转列pivot 、列转行unpivot 的Sql语句总结

多行转字符串

copy print?

1. select concat(id,username) str from app_user  
2.   
3. select id||username str from app_user  

wm_concat函数

首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据

copy print?

1. create table test(id number,name varchar2(20));  
2.   
3. insert into test values(1,'a');  
4. insert into test values(1,'b');  
5. insert into test values(1,'c');  
6. insert into test values(2,'d');  
7. insert into test values(2,'e');  

view plain copy print?

1. select wm_concat(name) name from test;  

view plain copy print?

1. select replace(wm_concat(name),',','|') from test;  

view plain copy print?

1. select id,wm_concat(name) name from test group by id;  

sql语句等同于下面的sql语句 [sql] view plain copy print?

1. -------- 适用范围：8i,9i,10g及以后版本  （ MAX + DECODE ）  
2. select id, max(decode(rn, 1, name, null)) || max(decode(rn, 2, ','||name, null)) || max(decode(rn, 3, ','||name, null)) str  
3.     from (select id, name ,row_number() over(partition by id order by name) as rn from test) t group by id order by 1;   
4.        
5. -------- 适用范围：8i,9i,10g及以后版本 （ ROW_NUMBER + LEAD ）  
6. select id, str from (select id,row_number() over(partition by id order by name) as rn,name || lead(',' || name, 1)  
7.     over(partition by id order by name) ||  lead(',' || name, 2) over(partition by id order by name) || lead(',' || name, 3)   
8.     over(partition by id order by name) as str from test) where rn = 1 order by 1;  
9.     
10. -------- 适用范围：10g及以后版本 （ MODEL ）  
11. select id, substr(str, 2) str from test model return updated rows partition by(id) dimension by(row_number()  
12.     over(partition by id order by name) as rn) measures (cast(name as varchar2(20)) as str) rules upsert iterate(3)  
13.     until(presentv(str[iteration_number + 2], 1, 0)=0) (str[0] = str[0] || ',' || str[iteration_number + 1]) order by 1;       
14.            
15. -------- 适用范围：8i,9i,10g及以后版本 （ MAX + DECODE ）  
16. select t.id id, max(substr(sys_connect_by_path(t.name, ','), 2)) str from (select id, name, row_number()   
17.     over(partition by id order by name) rn from test) t start with rn = 1 connect by rn = prior rn + 1 and id = prior id  
18.     group by t.id;  

view plain copy print?

1.  /** 这里的表名默认区分大小写 */  
2. select 'create or replace view as select '|| wm_concat(column_name) || ' from APP_USER' sqlStr   
3.        from user_tab_columns where table_name='APP_USER';    

利用系统表方式查询

[sql] view plain copy print?

1. select * from user_tab_columns  

Oracle 11g 行列互换 pivot 和 unpivot 说明

在Oracle 11g中，Oracle 又增加了2个查询：pivot（行转列） 和unpivot（列转行）

参考：http://blog.csdn.NET/tianlesoftware/article/details/7060306、http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html

google 一下，网上有一篇比较详细的文档：http://www.oracle-developer.net/display.php?id=506

view plain copy print?

1. create table demo(id int,name varchar(20),nums int);  ---- 创建表  
2. insert into demo values(1, '苹果', 1000);  
3. insert into demo values(2, '苹果', 2000);  
4. insert into demo values(3, '苹果', 4000);  
5. insert into demo values(4, '橘子', 5000);  
6. insert into demo values(5, '橘子', 3000);  
7. insert into demo values(6, '葡萄', 3500);  
8. insert into demo values(7, '芒果', 4200);  
9. insert into demo values(8, '芒果', 5500);  

分组查询 （当然这是不符合查询一条数据的要求的）

[sql] view plain copy print?

1. select name, sum(nums) nums from demo group by name  

行转列查询

[sql] view plain copy print?

1. select * from (select name, nums from demo) pivot (sum(nums) for name in ('苹果' 苹果, '橘子', '葡萄', '芒果'));  

注意： pivot（聚合函数 for 列名 in（类型））   ，其中 in(‘’) 中可以指定别名，in中还可以指定子查询，比如 select distinct code from customers

当然也可以不使用pivot函数，等同于下列语句，只是代码比较长，容易理解

[sql] view plain copy print?

1. ------ 多项子查询  
2. select * from （select sum(nums) 苹果 from demo where name='苹果'）,（select sum(nums) 橘子 from demo where name='橘子'）,  
3.        （select sum(nums) 葡萄 from demo where name='葡萄'）,（select sum(nums) 芒果 from demo where name='芒果'）;  
4.          
5. ------  decode 函数利用  
6. select sum(decode(name,'苹果',nums)) 苹果, sum(decode(name,'橘子',nums)) 橘子,   
7.        sum(decode(name,'葡萄',nums)) 葡萄, sum(decode(name,'芒果',nums)) 芒果 from demo  

view plain copy print?

1. create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);  
2.   
3. insert into Fruit values(1,'苹果',1000,2000,3300,5000);  
4. insert into Fruit values(2,'橘子',3000,3000,3200,1500);  
5. insert into Fruit values(3,'香蕉',2500,3500,2200,2500);  
6. insert into Fruit values(4,'葡萄',1500,2500,1200,3500);  
7. select * from Fruit  

列转行查询

[sql] view plain copy print?

1. select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )  

注意：  unpivot没有聚合函数，xiaoshou、jidu字段也是临时的变量

同样不使用unpivot也可以实现同样的效果，只是sql语句会很长，而且执行速度效率也没有前者高

[sql] view plain copy print?

1. select id, name ,'Q1' jidu, (select q1 from fruit where id=f.id) xiaoshou from Fruit f  
2. union  
3. select id, name ,'Q2' jidu, (select q2 from fruit where id=f.id) xiaoshou from Fruit f  
4. union  
5. select id, name ,'Q3' jidu, (select q3 from fruit where id=f.id) xiaoshou from Fruit f  
6. union  
7. select id, name ,'Q4' jidu, (select q4 from fruit where id=f.id) xiaoshou from Fruit f  

http://www.ibloger.net/article/260.html