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