Oracle XMLAGG 問題與在單元格中組合具有逗號分隔值的單元格

有一種情況，我正在嘗試使用 xmlagg 來獲取：

    | ID | name
row1 | 1  | apple
row2 | 1  | apple,pear,banana
row3 | 1  | apple,banana
row4 | 1  | pear,banana,orange
row5 | 2  | orange
row6 | 2  | orange,pear
row7 | 2  | pear, banana

到：

1 | apple,pear,banana,orange
2 | orange,pear,banana

這可能使用xmlagg嗎？謝謝

測試數據：

select * from t_ ;

C1   C2   C3
row1 1    apple
row2 1    apple,pear,banana
row3 1    apple,banana
row4 1    pear,banana,orange
row5 2    orange
row6 2    orange,pear
row7 2    pear,banana

詢問：

select 
c2 ||' | '|| rtrim ( 
  xmlagg( xmlelement( e, c3 || ',' ) ).extract('//text()')
, ','
) result
from
 (
   select distinct c2, regexp_substr(c3,'[^,]+', 1, level ) c3 from t_
   connect by regexp_substr(c3, '[^,]+', 1, level) is not null
 )
group by c2
;

RESULT
1 | apple,pear,orange,banana
2 | pear,orange,banana

LISTAGG() 也可以工作 -

select 
c2 ||' | '|| rtrim ( 
  listagg(c2 , ',') within group ( order by c2 )
) result
from
 (
   select distinct c2, regexp_substr(c3,'[^,]+', 1, level ) c3 from t_
   connect by regexp_substr(c3, '[^,]+', 1, level) is not null
 )
group by c2
;

RESULT
1 | apple,banana,orange,pear
2 | banana,orange,pear

Dbfiddle在這裡。

引用自：https://dba.stackexchange.com/questions/193565