Aggregate concatenation - returning grouped rows as delimited list in DB2, AS400

gistfile1.sql

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 was a great help! Thank you.

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