1

Have a situation where I'm trying to use xmlagg to get:

     | 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

to:

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

Is this possible using xmlagg? Thanks

1 Answer 1

0

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.

1
  • @pgfitzpatrick - You are very welcome!
    – stefan
    Commented Dec 20, 2017 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.