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

shaoqin

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

• -- drop table example_row_data;
  
• 
  
• create table example_row_data (
  
•        user_school varchar2(2000),
  
•        user_name varchar2(2000),
  
•        user_item varchar2(2000),
  
•        user_value number
  
• );
  
• 
  
• insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_1','Linux',20);
  
• insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_1','BI',2);
  
• insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_1','数据库',20);
  
• commit;
  
• insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_2','Linux',10);
  
• insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_2','BI',30);
  
• commit;
  
• insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_3','Linux',5);
  
• insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_3','BI',2);
  
• insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_3','数据库',6);
  
• insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_3','数据挖掘',20);
  
• commit;
  

　　

　　查看样例数据：
　　

• 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;
  
• 
  
• 类别     用户             科目             数值
  
• -------- ---------------- ---------------- ----
  
• 博客     iData_1          Linux            20
  
• 博客     iData_1          BI               2
  
• 博客     iData_1          数据库           20
  
• 博客     iData_2          Linux            10
  
• 博客     iData_2          BI               30
  
• 博客     iData_3          Linux            5
  
• 博客     iData_3          BI               2
  
• 博客     iData_3          数据库           6
  
• 博客     iData_3          数据挖掘         20
  
• 
  
• 9 rows selected
  

　　

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

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

　　

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

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