Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create a delimitted string from a query in DB2

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

like image 855
Jeff Stock Avatar asked Sep 16 '10 15:09

Jeff Stock


People also ask

How do I concatenate a query in DB2?

You can concatenate strings by using the CONCAT operator or the CONCAT built-in function. When the operands of two strings are concatenated, the result of the expression is a string. The operands of concatenation must be compatible strings.

How do I add a separator to a SQL query?

But what if you need to add a separator between each string? Fortunately, T-SQL provides the CONCAT_WS() function that helps you do exactly that. The CONCAT_WS() function works just like the CONCAT() function, except that it takes an extra argument – the separator you'd like to use.

What is Substr in DB2?

A substring of string-expression is zero or more contiguous characters of string-expression. If string-expression is a graphic string, a character is a DBCS character. If string-expression is a character string or a binary string, a character is a byte. The SUBSTR function accepts mixed data strings.


2 Answers

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)

like image 57
CanSpice Avatar answered Sep 21 '22 06:09

CanSpice


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;
like image 39
Andy Wilson Avatar answered Sep 17 '22 06:09

Andy Wilson