oracle 转置实现

浏览数：54 / 时间：2015年06月12日

下面是3种方法
方法1:SYS_CONNECT_BY_PATH , ROW_NUMBER() OVER(PARTITION BY .. ORDER BY ..) , START WITH , CONNECT BY PRIOR 组合使用
方法2:wmsys.wm_concat
方法3:listagg(oracle 11g release 2) 用法就像聚合函数一样,通过Group by语句,把每个Group的一个字段,拼接起来.

listagg 语法概述

listagg函数的语法结构如下：
LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

listagg虽然是聚合函数，但可以提供分析功能（比如可选的OVER()子句）。使用listagg中，下列中的元素是必须的：

需要聚合的列或者表达式
WITH GROUP 关键词
分组中的ORDER BY子句

下面将演示listagg函数使用的例子

例:
table1 中 1个col1对应多个col2,下面我们需要把col2转置如col2字段值为
2
3
4
需要变为 2-3-4 这样的格式,并且col2值是不可枚举的有上千或上万种,这样其他有些通过decode方式的转置就不能实现

使用方法1

 1 SELECT TT.col1,
 2        ‘-‘ || ‘:‘ ||
 3        TO_CHAR(SUBSTR(MAX(SYS_CONNECT_BY_PATH(TT.col2, ‘-‘)), 2)) M 
 4        --这里是为了截取掉 SYS_CONNECT_BY_PATH 在第一个值前加的"-"
 5   FROM (SELECT T.col1,
 6                T.col2,
 7                T.col1 + ROW_NUMBER() OVER(PARTITION BY T.col1 ORDER BY T.col2) RN,
 8                ROW_NUMBER() OVER(PARTITION BY T.col1 ORDER BY T.col2) RM
 9         --上面2行用了2次 ROW_NUMBER() 是因为 col1是累加的值所以一个 T.col1 + ROW_NUMBER() 是为了区别不同的分组,ROW_NUMBER() 这个是为了设置递归的起始值,但对于不同的分组都会有这个值"1"所以需要使用2个
10           FROM table1 T
11          WHERE /*T.col1 = TO_NUMBER(‘1013010875782363‘)*/) TT  --注释的部分是我测试用的
12  START WITH RM = 1
13 CONNECT BY PRIOR RN + 1 = RN
14  GROUP BY TT.col1 ;

方法2

select substr(tt.co, 1, length(tt.co) - 1), --去结尾"-"
       tt.col1
  from (select t.col1, replace(wmsys.wm_concat(t.col2 || ‘-‘), ‘,‘, null) co --去掉","
          from table1 t
        --WHERE T.col1 = TO_NUMBER(‘1013010875782363‘)
         group by t.col1) tt;

方法3

select population,  
       nation,  
       city,  
       listagg(city,‘,‘) within GROUP (order by city) over (partition by nation) rank  
from temp

或者

select nation,listagg(city,‘,‘) within GROUP (order by city)  
from temp  
group by nation

第二个虽然简单但是

wmsys.wm_concat对象实现行列转换的方法，这种方法不被Oracle所推荐，因为WMSYS用户用于Workspace Manager，其函数对象可能因版本而不同，这种变化在11.2.0.3及10.2.0.5中体现出来。原本WM_CONCAT函数返回值为VARCHAR2变更为CLOB。这一变化导致了很多程序的异常。
参考
http://www.eygle.com/archives/2012/10/wmsys_wm_concat.html

-------20150522 update--------

转置实现
说明:把 TF_B_TRADE_SP 里一个订单的SP增加或减少的 SP_PRODUCT_ID 转置后以一条行记录的形式列出

 1 SELECT TT.TRADE_ID,
 2        ‘-‘ || ‘,‘ ||
 3        SUBSTR(MAX(SYS_CONNECT_BY_PATH(TT.SP_PRODUCT_ID, ‘-‘)), 2)
 4   FROM (SELECT T.TRADE_ID,
 5                T.SP_PRODUCT_ID,
 6                T.MODIFY_TAG,
 7                T.TRADE_ID + ROW_NUMBER() OVER(PARTITION BY T.TRADE_ID ORDER BY T.SP_PRODUCT_ID) RN,
 8                ROW_NUMBER() OVER(PARTITION BY T.TRADE_ID ORDER BY T.SP_PRODUCT_ID) RM
 9           FROM TF_B_TRADE_SP T
10          WHERE T.TRADE_ID = TO_NUMBER(:VTRADE_ID)
11            AND T.MODIFY_TAG IN (‘1‘, ‘B‘)) TT
12  START WITH RM = 1
13 CONNECT BY PRIOR RN + 1 = RN
14  GROUP BY TT.TRADE_ID
15 UNION ALL
16 SELECT TT.TRADE_ID,
17        ‘+‘ || ‘,‘ ||
18        SUBSTR(MAX(SYS_CONNECT_BY_PATH(TT.SP_PRODUCT_ID, ‘-‘)), 2)
19   FROM (SELECT T.TRADE_ID,
20                T.SP_PRODUCT_ID,
21                T.MODIFY_TAG,
22                T.TRADE_ID + ROW_NUMBER() OVER(PARTITION BY T.TRADE_ID ORDER BY T.SP_PRODUCT_ID) RN,
23                ROW_NUMBER() OVER(PARTITION BY T.TRADE_ID ORDER BY T.SP_PRODUCT_ID) RM
24           FROM TF_B_TRADE_SP T
25          WHERE T.TRADE_ID = TO_NUMBER(:VTRADE_ID)
26            AND T.MODIFY_TAG IN (‘0‘, ‘A‘)) TT
27  START WITH RM = 1
28 CONNECT BY PRIOR RN + 1 = RN
29  GROUP BY TT.TRADE_ID;

View Code

修改后最后写好是这样

 1 SELECT MI.M || ‘,‘ || AD.A
 2   FROM (SELECT TT.TRADE_ID,
 3                ‘-‘ || ‘:‘ ||
 4                TO_CHAR(SUBSTR(MAX(SYS_CONNECT_BY_PATH(TT.SP_PRODUCT_ID, ‘_‘)),
 5                               2)) M
 6           FROM (SELECT T.TRADE_ID,
 7                        T.SP_PRODUCT_ID,
 8                        T.MODIFY_TAG,
 9                        T.TRADE_ID + ROW_NUMBER() OVER(PARTITION BY T.TRADE_ID ORDER BY T.SP_PRODUCT_ID) RN,
10                        ROW_NUMBER() OVER(PARTITION BY T.TRADE_ID ORDER BY T.SP_PRODUCT_ID) RM
11                   FROM TF_B_TRADE_SP T
12                  WHERE T.TRADE_ID = TO_NUMBER(‘1013010875782363‘)
13                    AND T.MODIFY_TAG IN (‘1‘, ‘B‘)) TT
14          START WITH RM = 1
15         CONNECT BY PRIOR RN + 1 = RN
16          GROUP BY TT.TRADE_ID) MI,
17        (SELECT TT.TRADE_ID,
18                ‘+‘ || ‘:‘ ||
19                TO_CHAR(SUBSTR(MAX(SYS_CONNECT_BY_PATH(TT.SP_PRODUCT_ID, ‘_‘)),
20                               2)) A
21           FROM (SELECT T.TRADE_ID,
22                        T.SP_PRODUCT_ID,
23                        T.MODIFY_TAG,
24                        T.TRADE_ID + ROW_NUMBER() OVER(PARTITION BY T.TRADE_ID ORDER BY T.SP_PRODUCT_ID) RN,
25                        ROW_NUMBER() OVER(PARTITION BY T.TRADE_ID ORDER BY T.SP_PRODUCT_ID) RM
26                   FROM TF_B_TRADE_SP T
27                  WHERE T.TRADE_ID = TO_NUMBER(‘1013010875782363‘)
28                    AND T.MODIFY_TAG IN (‘0‘, ‘A‘)) TT
29          START WITH RM = 1
30         CONNECT BY PRIOR RN + 1 = RN
31          GROUP BY TT.TRADE_ID) AD;

View Code

说明

 1 SELECT TT.col1,
 2        ‘-‘ || ‘:‘ ||
 3        TO_CHAR(SUBSTR(MAX(SYS_CONNECT_BY_PATH(TT.col2, ‘-‘)), 2)) M 
 4        --这里是为了截取掉 SYS_CONNECT_BY_PATH 在第一个值前加的"-"
 5   FROM (SELECT T.col1,
 6                T.col2,
 7                T.col1 + ROW_NUMBER() OVER(PARTITION BY T.col1 ORDER BY T.col2) RN,
 8                ROW_NUMBER() OVER(PARTITION BY T.col1 ORDER BY T.col2) RM
 9         --上面2行用了2次 ROW_NUMBER() 是因为 col1是累加的值所以一个 T.col1 + ROW_NUMBER() 是为了区别不同的分组,ROW_NUMBER() 这个是为了设置递归的起始值,但对于不同的分组都会有这个值"1"所以需要使用2个
10           FROM table1 T
11          WHERE /*T.col1 = TO_NUMBER(‘1013010875782363‘)*/) TT  --注释的部分是我测试用的
12  START WITH RM = 1
13 CONNECT BY PRIOR RN + 1 = RN
14  GROUP BY TT.col1 ;