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

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.

Answer 2

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 3

Try this:

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

Answer 4

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.

Answer 5

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

Answer 6

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