行转列的一个SQL写法（以Oracle为例）
在数据分析的过程中，常常会遇到行转列的问题。例如，系统按事务逐条摆放，但是分析时希望以某个线索（例如每一个客户）为中心，查看所有的事项。写个性化程序或者写自定义聚合函数都可以有很好的解决方案，但是计算环境复杂多变，有程序未必能部署上，掌握一个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)
[*]2FROM (SELECT t.user_school
[*]3 ,t.user_name
[*]4 ,COUNT(*) cnt
[*]5 FROM example_row_data t
[*]6 GROUPBY 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
[*]GROUPBY 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
页:
[1]