DB2 Comma Separated Output by Groups

Is there a built in function for comma separated column values in开发者_JS百科 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

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

My problem was to transpose row fields(CLOB) to column(VARCHAR) with a CSV and use the transposed table for reporting. Because transposing on report layer slows down the report.

One way to go is to use recursive SQL. You can find many articles about that but its difficult and resource consuming if you want to join all your recursive transposed columns.

I created multiple global temp tables where I stored single transposed columns with one key identifier. Eventually, I had 6 temp tables for joining 6 columns but due to limited resource allocation I wasnt able to bring all columns together. I opted to below 3 formulas and then I just had to run 1 query which gave me output in 10 seconds.

I found various articles on using XML2CLOB functions and have found 3 different ways.

REPLACE(VARCHAR(XML2CLOB(XMLAGG(XMLELEMENT(NAME "A",ALIASNAME.ATTRIBUTENAME)))),'', ',') AS TRANSPOSED_OUTPUT

NVL(TRIM(',' FROM REPLACE(REPLACE(REPLACE(CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME "E", ALIASNAME.ATTRIBUTENAME))) AS VARCHAR(100)),'',' '),'',','), '', 'Nothing')), 'Nothing') as TRANSPOSED_OUTPUT

RTRIM(REPLACE(REPLACE(REPLACE(VARCHAR(XMLSERIALIZE(XMLAGG(XMLELEMENT(NAME "A",ALIASNAME.ATTRIBUTENAME) ORDER BY ALIASNAME.ATTRIBUTENAME) AS CLOB)), '',','),'',''),'','')) AS TRANSPOSED_OUTPUT

Make sure you are casting your "ATTRIBUTENAME" to varchar in a subquery and then calling it here.

other possibility, with recursive cte

    with tablewithrank as (
    select id, category, rownumber() over(partition by category order by id) as rangid , (select count(*) from  myTable f2 where f1.category=f2.category) nbidbycategory
    from myTable f1
    ),
    cte (id, category, rangid, nbidbycategory, rangconcat) as (
    select id, category, rangid, nbidbycategory, cast(id as varchar(500)) from tablewithrank where rangid=1
    union all 
    select  f2.id, f2.category, f2.rangid, f2.nbidbycategory, cast(f1.rangconcat as varchar(500)) || ',' || cast(f2.id as varchar(500)) from cte f1 inner join tablewithrank f2 on f1.rangid=f2.rangid -1 and f1.category=f2.category
    )
    select category, rangconcat as IDS  from cte
    where rangid=nbidbycategory

Try this:

SELECT GROUP_CONCAT( field1, field2, field3 ,field4 SEPARATOR ', ')