Oracle 使用 listagg 多列群組合成同一列方法

2020-12-24 155 words One minute

Contents

之前有在 MySQL 用過這個需求用過這個需求GROUP_CONCAT 和 CONCAT_WS 多個橫欄和縱欄組在一起方法 | 程式狂想筆記
但是 Oracle 用上有差異
還多了一堆限制(如:字數不能超過 4000)
distinct 還會被禁用??!

Distinct

1
2
3
4


-- 此方法需要排序後才有過濾重複效果
select REGEXP_REPLACE('5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2', '(^|,)([^,]*)(,\2)+','\1\2') from dual;
---------------------------------
5,6,5,6,7,4,1,2,1,4,7,2

字數過長

1
2
3
4
5
6
7


SQL> select rtrim(xmlagg(xmlelement(e,id,',').extract('//text()') order by id).GetClobVal(),',')
   from (select level as id from dual connect by level < 1050)
  /

RTRIM(XMLAGG(XMLELEMENT(E,ID,',').EXTRACT('//TEXT()')ORDERBYID).GETCLOBVAL(),','
--------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18...

但這個沒有排序…

逗號排序

Sort delimited separated values in a string with (Oracle) SQL - Stack Overflow

1
2
3
4
5
6


select listagg(somedata, '|') within group (order by somedata) somedata from (
with q as (select '|'||'9|2|6|7|5' as somedata from dual)
select substr(somedata, instr(somedata, '|', 1, rownum) + 1, 1) somedata
  from q,
   (select 1 from q connect by level <= length(regexp_replace(somedata, '[0-9]', '')))
)

但這個跟 xmlagg 結合會有問題
都會跑出 null

先記錄到這邊