Create a delimitted string from a query in DB2

Question

I am trying to create a delimitted string from the results of a query in DB2 on the iSeries (AS/400). I've done this in T-SQL, but can't find a way to do it here.

Here is my code in T-SQL. I'm looking for an equivelant in DB2.

DECLARE @a VARCHAR(1000)
SELECT @a = COALESCE(@a + ', ' + [Description], [Description])
FROM AP.Checkbooks
SELECT @a

If the descriptions in my table look like this:

Desc 1
Desc 2
Desc 3

Then it will return this:

Desc 1, Desc 2, Desc 3

In MySQL, this would be 'GROUP_CONCAT()'. – Jonathan Leffler Sep 16 '10 at 15:47 — Jonathan Leffler, Sep 16 '10 at 15:47

score 37 · Answer 1 · answered Sep 16 '10 at 16:21

37

Essentially you're looking for the equivalent of MySQL's GROUP_CONCAT aggregate function in DB2. According to one thread I found, you can mimic this behaviour by going through the XMLAGG function:

create table t1 (num int, color varchar(10));

insert into t1 values (1,'red'), (1,'black'), (2,'red'), (2,'yellow'), (2,'green');

select num,
  substr( xmlserialize( xmlagg( xmltext( concat( ', ', color ) ) ) as varchar( 1024 ) ), 3 )
  from t1
  group by num;

This would return

1 red,black
2 red,yellow,green

(or should, if I'm reading things correctly)

answered Sep 16 '10 at 16:21

CanSpice

34,814
10
72
86

5

+1, side comment: If you are running on Z/OS v10, then change `as varchar(1024)` to `as clob(1024)` – Wojciech Owczarczyk May 14 '13 at 09:59
3

I've been looking for this for years. This works for exactly what I need. Thank you. – Eric Belair Jun 01 '16 at 19:56
1

There should be a +10 button for answers like this. – ramazan polat Nov 14 '19 at 06:50

score 1 · Answer 2 · answered Jul 03 '13 at 15:41

You can do this using common table expressions (CTEs) and recursion.

with                                                                
    cte1 as                                                             
        (select description, row_number() over() as row_nbr from checkbooks),

    cte2 (list, cnt, cnt_max) AS                              
        (SELECT VARCHAR('', 32000), 0, count(description) FROM cte1
         UNION ALL                                                        
         SELECT 
             -- No comma before the first description
             case when cte2.list = '' THEN RTRIM(CHAR(cte1.description)) 
                  else cte2.list || ', ' || RTRIM(CHAR(cte1.description)) end,   
                  cte2.cnt + 1, 
                  cte2.cnt_max                                
         FROM   cte1,cte2                                                 
         WHERE  cte1.row_nbr = cte2.cnt + 1 AND cte2.cnt < cte2.cnt_max ),                             

    cte3 as                                                          
        (select list from cte2 
         where cte2.cnt = cte2.cnt_max fetch first 1 row only)

select list from cte3;

score 0 · Answer 3 · answered Jan 29 '14 at 13:58

0

If you are running DB2 9.7 or higher, you can use LISTAGG function. Have a look here: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0058709.html

answered Jan 29 '14 at 13:58

Mensur

1,196
3
18
30

score 0 · Accepted Answer · answered Sep 16 '10 at 21:08

0

I'm trying to do this in OLEDB and from what I understand you can't do this because you can't do anything fancy in SQL for OLEDB like declare variables or create a table. So I guess there is no way.

answered Sep 16 '10 at 21:08

Jeff Stock

3,796
12
46
61

1

I'm not sure if this was meant to be a comment to my answer, but my answer doesn't declare variables or create tables. It could be that `XMLAGG` isn't available to you, though. – CanSpice Sep 16 '10 at 21:37
Correct, I guess I don't have XMLAGG, since it doesn't recognize whatever that is in an OLEDB query. – Jeff Stock Sep 19 '10 at 00:22
He can use a database function – Zo Has Feb 13 '13 at 10:15

Create a delimitted string from a query in DB2

4 Answers4

Linked