-
-
Save timothymolter/eda0907623524e5eb99b0e8b95e758a1 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
-- 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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