ORACLE行列转换

        在论坛上经常有人问有关行列转换的问题，现在整理总结一下：

实验所用的数据为ORACLE样例数据库的SH模式下的SALES表

一、行转列【把一列转为多列】

       需要注意的是，被转置列（下面例子的prod_id）只能有一列，而非转置列（如下面的cust_id）则可以则可以有多列.从转置列被转置后的列数是否固定可以分为下面两种情况：

第一、列数固定

转换前：

create table tab asselect cust_id,prod_id,amount_soldfrom (select cust_id,       prod_id,       amount_sold,       row_number()over (partition by prod_id order by prod_id) as numfrom (select  distinct cust_id,                  prod_id,                   amount_soldfrom 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.csdn.net/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语句

      利用上面的聚合函数的方法举例，PIVOT也可以。

第一步，把数据保存在一个临时表row_to_col中。

set serveroutput on;declare num int;str varchar2(1000);begin    select count(distinct prod_id) into num from tab;----判断该转置列是否有数据；    if num !=0 then------拼接SQL语句；       str :='select cust_id,';       for i in (select distinct prod_id from tab order by prod_id) loop            str:=str||'max(decode (prod_id,'||i.prod_id||',amount_sold,'''')) as "'||i.prod_id||'",';       end loop;       str :=rtrim(str,',');       str :=str||'  from tab group by cust_id';-------创建临时表，用于临时存储执行SELECT 语句返回的数据；         str :='create global temporary table row_to_column on commit preserve rows as  '||str;       EXECUTE immediate str;   else      dbms_output.put_line('转置列没有数据！');   end if;end;

第二步：查询临时表

SQL> select * from row_to_col;   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

二、列转行【多列转为一列】

      利用上面行转列的数据为实验数据创建表：col_to_row；

create table col_to_row asselect cust_id,       max(decode (prod_id,13,amount_sold,'')) as "13",       max(decode (prod_id,14,amount_sold,'')) as "14",       max(decode (prod_id,15,amount_sold,'')) as "15"from tab group by cust_id;

SQL> select * from col_to_row;   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

方法一：UNION ALL【8i开始】

select cust_id, 13 as prod_id,"13" as amount_sold from col_to_row where "13" is not nullunion allselect cust_id, 14 as prod_id,"14" as amount_sold from col_to_row where "14" is not nullunion allselect cust_id, 15 as prod_id,"15" as amount_sold from col_to_row where "15" is not null

   CUST_ID    PROD_ID AMOUNT_SOLD---------- ---------- -----------     36217         13      842.21      3075         13      842.21      2803         13      842.21      7425         14      935.79     20371         14      935.79      2671         14      935.79      1311         15      871.19      3178         15      871.19      5864         15      871.19

方法二：MODEL子句（10g开始）

        MODEL子句的语法，可以看我的博文：http://blog.csdn.net/flighting_sky/article/details/9527675

SQL> select * from (  2  select cust_id,prod_id,amount_sold from col_to_row  3  model return updated rows  4  partition by (cust_id)  5  dimension by (0 as n)  6  measures (1 as prod_id,1 as amount_sold,"13","14","15")  7  rules  8  (  9     prod_id[1]=13, 10     prod_id[2]=14, 11     prod_id[3]=15, 12     amount_sold[1]="13"[0], 13     amount_sold[2]="14"[0], 14     amount_sold[3]="15"[0] 15  )) 16  where amount_sold is not null 17  order by cust_id 18  ;   CUST_ID    PROD_ID AMOUNT_SOLD---------- ---------- -----------      1311         15      871.19      2671         14      935.79      2803         13      842.21      3075         13      842.21      3178         15      871.19      5864         15      871.19      7425         14      935.79     20371         14      935.79     36217         13      842.21

方法三：UNPIVOT（11g开始）

SQL> select cust_id,prod_id,amount_sold  2  from col_to_row  3  unpivot exclude nulls  4  (  5     amount_sold  6     for prod_id  7         in ("13" as 13,"14" as 14,"15" as 15)  8  )  9  order by prod_id;   CUST_ID    PROD_ID AMOUNT_SOLD---------- ---------- -----------      2803         13      842.21      3075         13      842.21     36217         13      842.21      7425         14      935.79     20371         14      935.79      2671         14      935.79      3178         15      871.19      1311         15      871.19      5864         15      871.19

三、多列连接成一列字符串

      利用上面行转列后的数据；

SQL> select * from col_to_row;   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

“||”或者concat()函数，不过concat()函数自能连接两个字符串：

SQL> select cust_id,"13"||"14"||"15" as link from col_to_row;

SQL>  select cust_id,concat(concat("13","14"),"15") as link from col_to_row;   CUST_ID LINK---------- --------------------     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

四、多行转换成字符串

      把下面数据中prod_id相同的amount_sold连接成字符串，用“/”分隔开来：

SQL> select * from tab;   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

方法一：利用上面行转列的第一种方法（MAX+DECODE）【8i开始】（此方法收到组数的不确定性影响，如果组数比较多，则相应的DECODE函数也会同样增多，到时只能采用动态语句拼接的方法）

SQL> select prod_id,  2         max(decode(num,1,amount_sold||'/',''))||  3         max(decode(num,2,amount_sold||'/',''))||  4         max(decode(num,3,amount_sold||'/','')) as link  5  from (  6  select prod_id,  7         amount_sold,  8         row_number()over(partition by prod_id order by amount_sold) num  9  from tab) 10  group by prod_id 11  order by prod_id;   PROD_ID LINK---------- -------------------------        13 842.21/842.21/842.21/        14 935.79/935.79/935.79/        15 871.19/871.19/871.19/

方法二：row_number+lead()【8i开始】（此方法受到同组元素个数的不确定性影响。由于无法确定同一组的元素有多少，因此lead的的位置参数最后要到多少，无法确定）

SQL> select prod_id,  2         link  3  from (  4  select prod_id,  5         row_number()over(partition by prod_id order by amount_sold) as num,  6         amount_sold||'/'||lead(amount_sold,1)over (partition by prod_id order by amount_sold)  7                    ||'/'||lead(amount_sold,2)over (partition by prod_id order by amount_sold) as link  8  from tab)  9  where num=1 10  order by prod_id;   PROD_ID LINK---------- -------------------------        13 842.21/842.21/842.21        14 935.79/935.79/935.79        15 871.19/871.19/871.19

方法三：MODEL子句（10g开始）

        利用MODEL子句的迭代方法来连接（此方法跟上面的方法二都需要row_number()来定位,都有有同样的约束，就是受到同组元素个数的约束，因此，难以确定需要迭代多少次）：

SQL> select prod_id,  2         link  3  from tab  4  model return updated rows  5  partition by (prod_id)  6  dimension by (row_number()over(partition by prod_id order by amount_sold) as num)  7  measures (cast('' as varchar(100)) as link,amount_sold)  8  rules iterate(3)  9  ( 10     link[0]=link[0]||'/'||amount_sold[iteration_number +1] 11  ) 12  order by prod_id;   PROD_ID LINK---------- -------------------------        13 /842.21/842.21/842.21        14 /935.79/935.79/935.79        15 /871.19/871.19/871.19

方法四：sys_connect_by_path()【8i开始】（不会受到组数和同组个数的不确定性影响）

SQL> with t as(  2  select prod_id,  3         amount_sold,  4         row_number() over(partition by prod_id order by amount_sold) as num  5  from tab  6  )  7  select prod_id,  8         max(sys_connect_by_path(t.amount_sold,'/')) as link  9  from t 10  start with num=1 11  connect by PRIOR num =num-1 12        and  PRIOR prod_id=prod_id 13  group by prod_id 14  order by prod_id;   PROD_ID LINK---------- -------------------------        13 /842.21/842.21/842.21        14 /935.79/935.79/935.79        15 /871.19/871.19/871.19

方法五：wmsys.wm_concat()函数【10g开始】（简洁，不受到组数和同组个数的不确定性影响）

SQL> select prod_id,  2         replace(wmsys.wm_concat(amount_sold),',','/') as link  3  from tab  4  group by prod_id  5  order by prod_id;   PROD_ID LINK---------- -------------------------        13 842.21/842.21/842.21        14 935.79/935.79/935.79        15 871.19/871.19/871.19

方法六：listagg()【11gR2开始】（跟上面的wmsys.wm_concat同样的有点，只是对于oracle的版本要求有点高）

SQL> select prod_id,  2           listagg(amount_sold,'/') within group (order by amount_sold) as link  3   from tab  4   group by prod_id  5   order by prod_id;   PROD_ID LINK---------- -------------------------        13 842.21/842.21/842.21        14 935.79/935.79/935.79        15 871.19/871.19/871.19

五、字符串拆分成多列

       我们要把之前多列合并成的字符串重新分开为多列，简单理解就是拆分字符串：

SQL>select 'a/b/c/d' from dual;

方法一：substr+instr【8i开始】(该方法容易受到列数的不确定性影响，如果需要拆分的列比较多，则需要拼接SQL语句实现)

SQL> select substr('a/b/c/d',1,instr('a/b/c/d'||'/','/',1,1)-1) as a,  2         substr('a/b/c/d',instr('a/b/c/d'||'/','/',1,1)+1,instr('a/b/c/d'||'/','/',1,2)-instr('a/b/c/d'||'/','/',1,1)-1) as b,  3         substr('a/b/c/d',instr('a/b/c/d'||'/','/',1,2)+1,instr('a/b/c/d'||'/','/',1,3)-instr('a/b/c/d'||'/','/',1,2)-1) as c,  4         substr('a/b/c/d',instr('a/b/c/d'||'/','/',1,3)+1,instr('a/b/c/d'||'/','/',1,4)-instr('a/b/c/d'||'/','/',1,3)-1) as d  5  from dual;A B C D- - - -a b c d

方法二：regexp_substr()【10g开始】(代码比上面的方法一有所简化，不过依然存在同样的约束)

SQL> select rtrim(regexp_substr('afg/b/c/d'||'/','.*?'||'/',1,1),'/') as a,  2         rtrim(regexp_substr('afg/b/c/d'||'/','.*?'||'/',1,2),'/') as b,  3         rtrim(regexp_substr('afg/b/c/d'||'/','.*?'||'/',1,3),'/') as c,  4         rtrim(regexp_substr('afg/b/c/d'||'/','.*?'||'/',1,4),'/') as d  5  from dual;A   B C D--- - - -afg b c d

六、字符串拆分成一列多行

       把上面多行转字符串得到的字符串拆分成一列多行：

方法一：UNION ALL【8i开始】（该方法实质是上利用上面字符串拆分成多列后的合并，存在同样的约束）

SQL> with t as(  2   select prod_id,  3             listagg(amount_sold,'/') within group (order by amount_sold) as link  4     from tab  5     group by prod_id  6     order by prod_id  7  )  8  select prod_id,  9         substr(link,1,instr(link||'/','/',1,1)-1) as amount_sold 10  from t 11  UNION ALL 12  select prod_id, 13         substr(link,instr(link||'/','/',1,1)+1,instr(link||'/','/',1,2)-instr(link||'/','/',1,1)-1) as amount_sold 14  from t 15  UNION ALL 16  select prod_id, 17         substr(link,instr(link||'/','/',1,2)+1,instr(link||'/','/',1,3)-instr(link||'/','/',1,2)-1) as amount_sold 18  from t  ;   PROD_ID AMOUNT_SOL---------- ----------        13 842.21        14 935.79        15 871.19        13 842.21        14 935.79        15 871.19        13 842.21        14 935.79        15 871.19 

利用regexp_substr()【10g开始】

SQL> with t as(  2   select prod_id,  3             listagg(amount_sold,'/') within group (order by amount_sold) as link  4     from tab  5     group by prod_id  6     order by prod_id  7  )  8  select prod_id,  9         rtrim(regexp_substr(link||'/','.*?'||'/',1,1),'/') as amount_sold 10  from t 11  UNION ALL 12  select prod_id, 13         rtrim(regexp_substr(link||'/','.*?'||'/',1,2),'/') as amount_sold 14  from t 15  UNION ALL 16  select prod_id, 17         rtrim(regexp_substr(link||'/','.*?'||'/',1,3),'/') as amount_sold 18  from t  ;   PROD_ID AMOUNT_SOL---------- ----------        13 842.21        14 935.79        15 871.19        13 842.21        14 935.79        15 871.19        13 842.21        14 935.79        15 871.19

方法二：使用connect by 和level

SQL>  with t as(  2     select prod_id,  3               listagg(amount_sold,'/') within group (order by amount_sold) as link  4       from tab  5       group by prod_id  6       order by prod_id  7    )  8  select  prod_id ,  9         max(substr('/'||link||'/' 10                ,instr('/'||link||'/','/',1,level)+1 11                ,instr('/'||link||'/','/',1,level+1) 12                -instr('/'||link||'/','/',1,level)-1)) as amount_sold 13  from t 14  connect by level <instr(link,'/',1,level-1)+1 15  group by prod_id,level 16  order by prod_id;   PROD_ID AMOUNT_SOL---------- ----------        13 842.21        13 842.21        13 842.21        14 935.79        14 935.79        14 935.79        15 871.19        15 871.19        15 871.19