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.