DB2 Comma Separated Output by Groups

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

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

Depending of the DB2 version you have, you can use XML functions to achieve this.

Example table with some data

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

Aggregate results using xml functions

select category, 
    xmlserialize(XMLAGG(XMLELEMENT(NAME "x", id) ) as varchar(1000)) as ids 
    from myTable
    group by category;

results:

CATEGORY IDS
 -------- ------------------------
        1 <x>1</x><x>3</x><x>5</x>
        2 <x>2</x><x>4</x>

Use replace to make the result look better

select category, 
        replace(
        replace(
        replace(
            xmlserialize(XMLAGG(XMLELEMENT(NAME "x", id) ) as varchar(1000))
            , '</x><x>', ',')
            , '<x>', '')
            , '</x>', '') as ids 
    from myTable
    group by category;

Cleaned result

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

Just saw a better solution using XMLTEXT instead of XMLELEMENT here.

Since DB2 9.7.5 there is a function for that:

LISTAGG(colname, separator)

check this for more information: Using LISTAGG to Turn Rows of Data into a Comma Separated List

DB2 Comma Separated Output by Groups

Related

Recent Posts