oracle 行转列分析函数

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

oracle 行转列首先看一下源数据：

技术分享

方法一：WM_CONCAT group by 这个方法没有问题。

SELECT CODE_TS,
            WMSYS.WM_CONCAT(S_NUM + 1 || ‘:‘ || ELEMENT) ELEMENT
        FROM T_MERCH_ELEMENT where code_ts=‘020745‘  group by CODE_TS;

得到的结果：

技术分享

上面大家可能会发现序号没有按顺序排列下来。如果没有要求，就这样就可以了。如果要排序看方法二。

方法二：WM_CONCAT OVER 有特殊数据时会报错。报：“操作数值超出系统的限制”。原因我不知道，谁知道麻烦告诉我一下。

select * from (  
   
SELECT CODE_TS,
            WMSYS.WM_CONCAT(S_NUM + 1 || ‘:‘ || ELEMENT) OVER(ORDER BY S_NUM) ELEMENT,
             TO_NUMBER(DENSE_RANK()
                       OVER(PARTITION BY CODE_TS ORDER BY S_NUM DESC)) ORDERNO
        FROM T_MERCH_ELEMENT where code_ts=‘020745‘ ORDER BY CODE_TS,S_NUM
        
        )t where t.ORDERNO=1;

WM_CONCAT OVER

如果不报错效果：

技术分享

方法三：sys_connect_by_path over 这个方法没有问题。但有注意点：sys_connect_by_path 这里的连接符号不要使用逗号，oracle会报错，如果一定要用，可以使用replace替换一下；连接符不可以在你的第一个参数列中存在。你可以用多个组合连接符。

那样就不容易在你的列中存在，我这里用了;加空格。当然你也可以用一个肯定不存在的组合连接符，然后把不需要的部分连接符replace掉

select CODE_TS,ELEMENT from 
(
        select CODE_TS,ELEMENT,
  row_number() over(partition by CODE_TS order by CODE_TS,rank desc) ename_path_rank
  from (
  
 
     select CODE_TS,rank,
                 ltrim(sys_connect_by_path(rank||‘:‘||ELEMENT,‘; ‘),‘; ‘) ELEMENT  from
                 (
                   select CODE_TS,ELEMENT,row_number() over(partition by CODE_TS order by s_num desc) rank
                   from T_MERCH_ELEMENT    where code_ts=‘020745‘           
                    order by s_num desc
                 ) connect by CODE_TS = prior CODE_TS and rank-1 = prior rank            
       )
       )       
where ename_path_rank=1;