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

gistfile-r8.sql

-- using db2 newer versions

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 ) ccsid 37 ), 3 )
from t1
group by num;

--OR without space after comma
select num,
      substr( xmlserialize( xmlagg( xmltext( concat( ',', color ) ) ) as varchar( 1024 ) ccsid 37 ), 2 )
from t1
group by num;


-- using older db2 versions

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( replace( replace( xmlserialize(content xmlagg( xmlelement(name "color", color)) as varchar(1024) ccsid 37 ), '<color>',', '), '</color>',''), 3) 
from t1 
group by num;

--OR without space after comma
select num,
      substr( replace( replace( xmlserialize(content xmlagg( xmlelement(name "color", color)) as varchar(1024) ccsid 37 ), '<color>',', '), '</color>',''), 2) 
from t1 
group by num;


-- source: https://www.ibm.com/developerworks/community/forums/thread.jspa?threadID=143653
-- OR
-- source: https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000013892776

-- example for emails: https://s3.amazonaws.com/periscope-blog-assets/mysql-split-csv/dashboards-table.png

Posted a link to this code on LinkedIn for the 'RPG & DB2 Summit Alumni Forum' at:
https://www.linkedin.com/groups/1780278/1780278-6329772390809247747

Also posted a link to this code on the Infor "XA Community Discussions" at:
http://www.inforcommunities.com/p/fo/st/thread=9194&post=31636&posted=1#p31636