Oracle XMLAGG issue with combining cells that have comma separated values in cells
Test data:

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


Query:

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() may also work -

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 here.