DB2 Comma Separated Output by Groups

Question

Is there a built in function for comma separated column values in DB2 SQL?

Example: If there are columns with an ID and it has 3 rows with the same ID but have three different roles, the data should be concatenated with a comma.

ID   | Role ------------ 4555 | 2 4555 | 3 4555 | 4 

The output should look like the following, per row:

4555 2,3,4

Answer 1

LISTAGG function is new function in DB2 LUW 9.7

see example:

create table myTable (id int, category int);  insert into myTable values (1, 1); insert into myTable values (2, 2); insert into myTable values (5, 1); insert into myTable values (3, 1); insert into myTable values (4, 2); 

example: select without any order in grouped column

select category, LISTAGG(id, ', ') as ids from myTable group by category; 

result:

CATEGORY  IDS --------- ----- 1         1, 5, 3 2         2, 4 

example: select with order by clause in grouped column

select   category,   LISTAGG(id, ', ') WITHIN GROUP(ORDER BY id ASC) as ids from myTable group by category; 

result:

CATEGORY  IDS --------- ----- 1         1, 3, 5 2         2, 4 

Answer 2

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