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
    Dec 20, 2017 at 22:38

Your Answer

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

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