行转列的一个SQL写法（以Oracle为例）

在数据分析的过程中，常常会遇到行转列的问题。例如，系统按事务逐条摆放，但是分析时希望以某个线索（例如每一个客户）为中心，查看所有的事项。写个性化程序或者写自定义聚合函数都可以有很好的解决方案，但是计算环境复杂多变，有程序未必能部署上，掌握一个SQL写法快速响应大多数分析场景还是很有必要的。本文以Oracle为例，主要使用了row_number函数(不使用rank函数的原因是不希望出现2条记录排名相同的情况，我们的最终目的是行转列，不是合理排名)。

下列sql准备一下测试数据：

1. -- drop table example_row_data;  
2.  
3. create table example_row_data (  
4.        user_school varchar2(2000),  
5.        user_name varchar2(2000),  
6.        user_item varchar2(2000),  
7.        user_value number         
8. );  
9.  
10. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_1','Linux',20);  
11. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_1','BI',2);  
12. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_1','数据库',20);  
13. commit;  
14. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_2','Linux',10);  
15. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_2','BI',30);  
16. commit;  
17. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_3','Linux',5);  
18. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_3','BI',2);  
19. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_3','数据库',6);  
20. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_3','数据挖掘',20);  
21. commit; 

 查看样例数据：

1. SQL> select substr(t.user_school,1,4) 类别,substr(user_name,1,8) 用户 ,substr(t.user_item,1,8) 科目,substr(t.user_value,1,2) 数值 from example_row_data t;  
2.    
3. 类别     用户             科目             数值  
4. -------- ---------------- ---------------- ----  
5. 博客     iData_1          Linux            20  
6. 博客     iData_1          BI               2  
7. 博客     iData_1          数据库           20  
8. 博客     iData_2          Linux            10  
9. 博客     iData_2          BI               30  
10. 博客     iData_3          Linux            5  
11. 博客     iData_3          BI               2  
12. 博客     iData_3          数据库           6  
13. 博客     iData_3          数据挖掘         20  
14.    
15. 9 rows selected 

行转列前的准备工作，计算单个用户最大可能的科目数：

1. SQL> SELECT MAX(cnt)  
2.   2  FROM   (SELECT t.user_school  
3.   3                ,t.user_name  
4.   4                ,COUNT(*) cnt  
5.   5          FROM   example_row_data t  
6.   6          GROUP  BY t.user_school  
7.   7                   ,t.user_name) t;  
8.    
9.   MAX(CNT)  
10. ----------  
11.          4 

行转列（已知最大科目数为4）：

1. SELECT substr(t.user_school, 1, 4) 类别  
2.       ,substr(t.user_name, 1, 8) 用户  
3.       ,MAX(decode(rk, 1, rpad(t.user_item || ':' || t.user_value, 14, ' '), NULL)) ||  
4.        MAX(decode(rk, 2, rpad(t.user_item || ':' || t.user_value, 14, ' '), NULL)) ||  
5.        MAX(decode(rk, 3, rpad(t.user_item || ':' || t.user_value, 14, ' '), NULL)) ||  
6.        MAX(decode(rk, 4, rpad(t.user_item || ':' || t.user_value, 14, ' '), NULL)) 科目成绩  
7. FROM   (SELECT t.*  
8.               ,row_number() over(PARTITION BY t.user_school, t.user_name ORDER BY t.user_item) rk  
9.         FROM   example_row_data t) t  
10. GROUP  BY t.user_school  
11.          ,t.user_name;  
12.  
13. 类别     用户             科目成绩  
14. -------- ---------------- ---------------------------------------------------  
15. 博客     iData_1          BI:2          Linux:20      数据库:20  
16. 博客     iData_2          BI:30         Linux:10  
17. 博客     iData_3          BI:2          Linux:5       数据库:6      数据挖掘:20