首页 / ORACLE / oracle 转置实现

oracle 转置实现

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了oracle 转置实现，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4667字，纯文字阅读大概需要7分钟。

内容图文

下面是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 在第一个值前加的"-" 5FROM (SELECT T.col1,
 6               T.col2,
 7                T.col1 + ROW_NUMBER() OVER(PARTITION BY T.col1 ORDERBY T.col2) RN,
 8                ROW_NUMBER() OVER(PARTITION BY T.col1 ORDERBY T.col2) RM
 9--上面2行用了2次 ROW_NUMBER() 是因为 col1是累加的值所以一个 T.col1 + ROW_NUMBER() 是为了区别不同的分组,ROW_NUMBER() 这个是为了设置递归的起始值,但对于不同的分组都会有这个值"1"所以需要使用2个10FROM table1 T
11WHERE/*T.col1 = TO_NUMBER(‘1013010875782363‘)*/) TT  --注释的部分是我测试用的12  START WITH RM =113 CONNECT BY PRIOR RN +1= RN
14GROUPBY 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‘)groupby t.col1) tt;

方法3

            select
             population,  
       nation,  
       city,  
       listagg(city,
            ‘
            ,
            ‘) within GROUP (orderby city) over (partition by nation) rank  
fromtemp

或者

            select nation,listagg(city,‘,‘) within GROUP (orderby city)  
fromtempgroupby 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)
 4FROM (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 ORDERBY T.SP_PRODUCT_ID) RN,
 8                ROW_NUMBER() OVER(PARTITION BY T.TRADE_ID ORDERBY T.SP_PRODUCT_ID) RM
 9FROM TF_B_TRADE_SP T
10WHERE T.TRADE_ID = TO_NUMBER(:VTRADE_ID)
11AND T.MODIFY_TAG IN (‘1‘, ‘B‘)) TT
12  START WITH RM =113 CONNECT BY PRIOR RN +1= RN
14GROUPBY TT.TRADE_ID
15UNIONALL16SELECT TT.TRADE_ID,
17‘+‘||‘,‘||18        SUBSTR(MAX(SYS_CONNECT_BY_PATH(TT.SP_PRODUCT_ID, ‘-‘)), 2)
19FROM (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 ORDERBY T.SP_PRODUCT_ID) RN,
23                ROW_NUMBER() OVER(PARTITION BY T.TRADE_ID ORDERBY T.SP_PRODUCT_ID) RM
24FROM TF_B_TRADE_SP T
25WHERE T.TRADE_ID = TO_NUMBER(:VTRADE_ID)
26AND T.MODIFY_TAG IN (‘0‘, ‘A‘)) TT
27  START WITH RM =128 CONNECT BY PRIOR RN +1= RN
29GROUPBY TT.TRADE_ID;
View Code

 

修改后最后写好是这样

技术分享 技术分享 
                 1
                SELECT MI.M ||‘,‘|| AD.A
 2FROM (SELECT TT.TRADE_ID,
 3‘-‘||‘:‘|| 4                TO_CHAR(SUBSTR(MAX(SYS_CONNECT_BY_PATH(TT.SP_PRODUCT_ID, ‘_‘)),
 52)) M
 6FROM (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 ORDERBY T.SP_PRODUCT_ID) RN,
10                        ROW_NUMBER() OVER(PARTITION BY T.TRADE_ID ORDERBY T.SP_PRODUCT_ID) RM
11FROM TF_B_TRADE_SP T
12WHERE T.TRADE_ID = TO_NUMBER(‘1013010875782363‘)
13AND T.MODIFY_TAG IN (‘1‘, ‘B‘)) TT
14          START WITH RM =115         CONNECT BY PRIOR RN +1= RN
16GROUPBY TT.TRADE_ID) MI,
17        (SELECT TT.TRADE_ID,
18‘+‘||‘:‘||19                TO_CHAR(SUBSTR(MAX(SYS_CONNECT_BY_PATH(TT.SP_PRODUCT_ID, ‘_‘)),
202)) A
21FROM (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 ORDERBY T.SP_PRODUCT_ID) RN,
25                        ROW_NUMBER() OVER(PARTITION BY T.TRADE_ID ORDERBY T.SP_PRODUCT_ID) RM
26FROM TF_B_TRADE_SP T
27WHERE T.TRADE_ID = TO_NUMBER(‘1013010875782363‘)
28AND T.MODIFY_TAG IN (‘0‘, ‘A‘)) TT
29          START WITH RM =130         CONNECT BY PRIOR RN +1= RN
31GROUPBY 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 在第一个值前加的"-" 5FROM (SELECT T.col1,
 6               T.col2,
 7                T.col1 + ROW_NUMBER() OVER(PARTITION BY T.col1 ORDERBY T.col2) RN,
 8                ROW_NUMBER() OVER(PARTITION BY T.col1 ORDERBY T.col2) RM
 9--上面2行用了2次 ROW_NUMBER() 是因为 col1是累加的值所以一个 T.col1 + ROW_NUMBER() 是为了区别不同的分组,ROW_NUMBER() 这个是为了设置递归的起始值,但对于不同的分组都会有这个值"1"所以需要使用2个10FROM table1 T
11WHERE/*T.col1 = TO_NUMBER(‘1013010875782363‘)*/) TT  --注释的部分是我测试用的12  START WITH RM =113 CONNECT BY PRIOR RN +1= RN
14GROUPBY TT.col1 ;
View Code

 

 

 

原文：http://www.cnblogs.com/blogabc/p/3068992.html

内容总结

以上是互联网集市为您收集整理的oracle 转置实现全部内容，希望文章能够帮你解决oracle 转置实现所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/1193503.html

来源：【匿名】