oracle 行轉列 分析函數

標籤：

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;

sys_connect_by_path

效果如：

 

oracle 行轉列 分析函數