oracle sql string join and split string to rows

這篇主要寫一下用 oracle sql  把多筆變一筆，像 javascript 的 join 一樣。
還有 反過來說，怎麼把一筆切成很多筆.....

雖然是兩種不同的case，但是剛好最近一起撞到了，就乾脆合在一起寫一寫...

比較常用的是，多筆合成一欄資料。
首先要注意的是 oracle db 的版本
多筆資料，合成一筆的是用 listagg，版本是 11.2 釋出
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030

ex:
select listagg(XX_NAME, ',') within group (order by XX_NO)
from XXXX
從 XXXX 裡撈出， 每個 XX_NAME 依 XX_NO 排序，並用 「,」合併連接

============================================
至於接下來的把一欄變很多欄，就比較少見了...而且作法其實有很多種....
google keyword: oracle sql split string into rows
http://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle
我最後選的是 xmltable 的作法，因為真的看起來最簡潔，而且分隔符號最好自訂
不過說真的，我完全看不懂 xmltable在幹麻就是一_一|||...
要一般常見範例的話，keyword: oracle xmltable split ，其實蠻多的...

然後，因為我要處理的 case 其實很機車...他的原始格式類似是「東西;價錢;東西;價錢;...」
兩兩一組，還不一定是幾組...只是，就居然寫出來了Orz...
套句暗黑法師的話：連我自己都很佩服自己！

主要用到 with (since 9.2)，xmltable (since 10g) 的語法
我用的大概是類似底下這樣～

with DATA as (
     select XX_NO,
            '"'||replace(XX_DESC,';','","')||'"' as str
     from XXXX   
)

select a.XX_NO as XX_NO , a.STR2 as XX_NAME, to_number(b.STR2) as XX_PRICE
  from (select XX_NO, trim(column_value) STR2, rownum SN
             from DATA, xmltable(str) ) a,
       (select XX_NO, trim(column_value) STR2, rownum SN
             from DATA, xmltable(str) ) b
 where mod(a.SN, 2) = 1
       and b.XX_NO = a.XX_NO
       and b.SN = a.SN+1
       and regexp_like(b.STR2, '^-?\d+(\.\d+)?$')

先在 with 裡面處理 置換內容，變成可以做為 xmltable split 的 字串 （結果要像 「"A","100","B","200"...」做成 DATA.str）

然後在底下， 用 trim(column_value) ... from DATA, xmltable(str) 做出split 的效果（在此一列就會被拆成多列了）

最來要再處理歸類的問題...撈出來的，要做兩兩一組，所以基本上就以順序(rownum)來說，1,2是一組，3,4是一組，用 兩團原始資料 (a, b) ，再加 mod 跟 b.SN=a.SN+1 歸類完畢...

在最後，為了確認是數字格式（唉，反正這種非嚴格限制的，一定會有人給你亂塞資料）
用  regexp_like(b.STR2, '^-?\d+(\.\d+)?$')   判定避免 to_number 時造成的問題