在论坛上经常有人问有关行列转换的问题,现在整理总结一下:
实验所用的数据为ORACLE样例数据库的SH模式下的SALES表
一、行转列【把一列转为多列】
需要注意的是,被转置列(下面例子的prod_id)只能有一列,而非转置列(如下面的cust_id)则可以则可以有多列.从转置列被转置后的列数是否固定可以分为下面两种情况:
第一、列数固定
转换前:
create table tab as
select cust_id,prod_id,amount_sold
from (
select cust_id,
prod_id,
amount_sold,
row_number()over (partition by prod_id order by prod_id) as num
from
(select distinct cust_id,
prod_id,
amount_sold
from sales where prod_id<16
)
order by prod_id, amount_sold)
where num <4;
CUST_ID PROD_ID AMOUNT_SOLD
---------- ---------- -----------
36217 13 842.21
3075 13 842.21
2803 13 842.21
2671 14 935.79
7425 14 935.79
20371 14 935.79
3178 15 871.19
5864 15 871.19
1311 15 871.19
1、聚合函数(8i开始)
SQL> select cust_id,
2 max(decode (prod_id,13,amount_sold,'')) as "13",
3 max(decode (prod_id,14,amount_sold,'')) as "14",
4 max(decode (prod_id,15,amount_sold,'')) as "15"
5 from tab group by cust_id;
CUST_ID 13 14 15
---------- ---------- ---------- ----------
36217 842.21
3075 842.21
7425 935.79
1311 871.19
20371 935.79
2803 842.21
2671 935.79
3178 871.19
5864 871.19
聚合函数,还可以是sum()等。需要注意的是,上面的是一个cust_id对应一个prod_id只有一个数据的情况,如果有多个数据,则要根据业务需求,谨慎选取聚合函数。2、PIVOT(11g开始)
感觉PIVOT就是上面第一种方法的逻辑体现。具体关于PIVOT 语法的,请看博文:http://blog.youkuaiyun.com/flighting_sky/article/details/10123777
SQL> select * from tab
2 pivot
3 (
4 max(amount_sold)
5 for prod_id
6 in (13 as "13",14 as "14",15 as "15")
7 );
CUST_ID 13 14 15
---------- ---------- ---------- ----------
36217 842.21
3075 842.21
7425 935.79
1311 871.19
20371 935.79
2803 842.21
2671 935.79
3178 871.19
5864 871.19
第二、列数动态变化
1、拼接产生SQL语句