Created
January 25, 2012 11:08
-
-
Save zarkosusnjar/1675843 to your computer and use it in GitHub Desktop.
Aggregate concatenation - returning grouped rows as delimited list in DB2, AS400
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table t1 (num int, color varchar(10)); | |
insert into t1 values (1,'red'), (1,'black'), (2,'red'), (2,'yellow'), (2,'green'); | |
select num, | |
substr( xmlserialize( xmlagg( xmltext( concat( ', ', color ) ) ) as varchar( 1024 ) ), 3 ) | |
from t1 | |
group by num; | |
--OR without space after comma | |
select num, | |
substr( xmlserialize( xmlagg( xmltext( concat( ',', color ) ) ) as varchar( 1024 ) ), 2 ) | |
from t1 | |
group by num; | |
-- source: http://www.ibm.com/developerworks/forums/thread.jspa?threadID=143653 |
It helped me.
Thanks.
Thanks a lot, it was what i had been looking for, from many years ago.
the source link does not work anymore to: http://www.ibm.com/developerworks/forums/thread.jspa?threadID=143653
the new source link is to: https://www.ibm.com/developerworks/community/forums/thread.jspa?threadID=143653
or the full link is to: https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000013892776
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
It was a great help! Thank you.