DB2 comma separated output

Question

How to get inbuilt function for comma separated column values in sql in DB2 , e.g if there are columns with this policy id and it has 3 rows with the same id but have three different roles in three rows , then it should retrieve the rows in one row "3,4,5"

e.g.

1. 4555 "2"
2. 4555 "3"
3. 4555 "4"

output 4555 2,3,4 in a DB2 in one row

Answer 1

I think with this smaller query, you can do what you want. This is equivalent of MySQL's GROUP_CONCAT in DB2.

SELECT 
NUM, 
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',ROLES))) as VARCHAR(1024)), 3) as ROLES
FROM mytable 
GROUP BY NUM;

This will output something like:

NUM   ROLES
----  -------------
1     111, 333, 555
2     222, 444

assumming your original result was something like that:

NUM   ROLES
----  ---------
1     111
2     222
1     333
2     444
1     555