DB2 Concatenate rows

Question

I'm trying to query the iSeries DB2 v6r1m0. I'd like to borrow answers from Concatenate many rows into a single text string?

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL

I have attempted to apply it to the query below, but failed. I'm trying to concatenate the comments.

ATOMIC throws an error. It was taken from one answer somewhere. @comments and comments do not work. Section is a valid token. Not sure what that means.

BEGIN ATOMIC
DECLARE @comments varchar(100)
SELECT
    mh.ID
    ,mh.OtherStuff
    ,me.ID
    ,@Comments = COALESCE(@Comments + '<br />' ,") + me.Comment


FROM
    /*insertTblName*/ mh

INNER JOIN 
    /*insertTblName*/ me
    ON 
        me.ID = mh.ID
WHERE
    me.ID = @parameter
END

I'm trying to accomplish this.

ID      Comment
0       Hello
0       World
1       Foo
1       Bar

To....

ID     Comment
0      Hello World
1      Foo Bar

I usually test my SQL statements in System i Navigator before using them in ADO.Net.

You may want to use DB2 SQL instead of SQL Server T-SQL when working with DB2. — mustaccio, Jun 11 '15 at 20:12
v6.1 does not support compound statements (ie BEGIN...END) except in a stored procedure or UDF. Also, how are you executing your SQL? iNavigator? STRSQL? — WarrenT, Jun 11 '15 at 20:17
Your referenced link contains an example of a 'recursive CTE'. That's the most likely form that will work against DB2 for i 6.1. It's very likely that the T-SQL you tried will not work since a lot of T-SQL is not cross-platform but "Windows-only". — user2338816, Jun 12 '15 at 04:34
Warning! SQL (_by definition_) has no concept of in-table order. You'll get `World Hello` 50% of the time, unless you have some third column to do the ordering by. — Clockwork-Muse, Jun 13 '15 at 14:17

score 1 · Answer 1 · answered Jun 12 '15 at 19:36

Try with this example and data to understand the process and let me know if you solve it.

CREATE TABLE QTEMP/EMP (DEPTNO NUMERIC NOT NULL WITH DEFAULT, ENAME
CHAR ( 10) NOT NULL WITH DEFAULT, EMPNO NUMERIC NOT NULL WITH      
DEFAULT);                                                           


insert into emp values (10,'CLARK ',1),  
                       (10,'KING  ',2),  
                       (10,'MILLER',3),  
                       (20,'SMITH ',4),  
                       (20,'ADAMS ',5),  
                       (20,'FORD  ',6),  
                       (20,'SCOTT ',7),  
                       (20,'JONES ',8),  
                       (30,'ALLEN ',9),  
                       (30,'BLAKE ',10), 
                       (30,'MARTIN',11), 
                       (30,'JAMES ',12), 
                       (30,'TURNER',13), 
                       (30,'WARD  ',14)  

with x (deptno, cnt, list, empno, len)                             
 as (                                                               
select z.deptno, (select count(*) from emp y where y.deptno=z.deptno                                                   group by y.deptno)   
, cast(ename as varchar(100)), empno, 1                             
 from emp z                                                         
 union all                                                          
 select x.deptno, x.cnt, x.list ||' '|| e.ename, e.empno, x.len+1   
 from emp e, x                                                      
 where e.deptno = x.deptno                                          
 and e.empno > x. empno                                             
 )                                                                  
 select deptno, list                                                
 from x                                                             
 where len=cnt

This is the result.

DEPTNO   LIST                                                                        
    10   CLARK      KING       MILLER                                                
    20   SMITH      ADAMS      FORD       SCOTT      JONES                           
    30   ALLEN      BLAKE      MARTIN     JAMES      TURNER     WARD

score 0 · Answer 2 · answered Jul 01 '16 at 22:18

Excellent code, JairoFloresS! It worked beautifully for me.

I just slightly modified your code to make it more generic: I dropped the arbitrary column and instead use relative record number to keep track of original records.

It still works the same (at least on V7R1)!

CREATE TABLE QTEMP/EMP 
(
  DEPTNO NUMERIC    NOT NULL WITH DEFAULT,
  ENAME CHAR ( 10)  NOT NULL WITH DEFAULT
);  


insert into emp values (10,'CLARK '),  
                       (10,'KING  '),  
                       (10,'MILLER'),  
                       (20,'SMITH '),  
                       (20,'ADAMS '),  
                       (20,'FORD  '),  
                       (20,'SCOTT '),  
                       (20,'JONES '),  
                       (30,'ALLEN '),  
                       (30,'BLAKE '), 
                       (30,'MARTIN'), 
                       (30,'JAMES '), 
                       (30,'TURNER'), 
                       (30,'WARD  ')  
;


-- Original data:
select * from qtemp/emp  ;


-- Pivoted and grouped data:
with x (deptno, cnt, list, empno, len) as
 (select z.deptno, 
   (select count(*) from emp y 
    where y.deptno=z.deptno 
    group by y.deptno),
  cast(z.ename as varchar(100)),
  rrn(z), 1  
  from emp z 
 union all  
  select x.deptno,
     x.cnt,
     strip(x.list) ||', '|| e.ename,
     rrn(e),
     x.len+1   
  from emp e, x 
  where e.deptno = x.deptno and rrn(e) > x. empno 
 )  
 select deptno, list, len headcount 
 from x 
 where len=cnt 
;

Output produced looks like so:

Original data:

DEPTNO  ENAME
10      CLARK       
10      KING        
10      MILLER      
20      SMITH       
20      ADAMS       
20      FORD        
20      SCOTT       
20      JONES       
30      ALLEN       
30      BLAKE       
30      MARTIN      
30      JAMES       
30      TURNER      
30      WARD

Pivoted and grouped data:

DEPTNO  LIST                                    HEADCOUNT
10      CLARK, KING, MILLER                             3   
20      SMITH, ADAMS, FORD, SCOTT, JONES                5       
30      ALLEN, BLAKE, MARTIN, JAMES, TURNER, WARD       6

Be aware that RRN() will not guarantee that rows are accessed in a desired order **in the future**. There is no guarantee that rows will be added in RRN() sequence in a SQL table. An ORDER BY clause that references a column in the table should be included. — user2338816, Jul 02 '16 at 00:05
@user2338816 -- you are correct in that RRNs may just be random integers. And not just in the future, it can happen right now, if the originating table (aka physical file) was defined with REUSEDLT(*YES). Nevertheless, this code does not use RRNs to sort, only to identify unique records, so it should work even when RRNs are random. Of cause, if any particular order in the result set is desired, ORDER BY is the way to do it. — Mip Jones, Jul 05 '16 at 23:44
The example shows words concatenated matching the order listed in the sample data. My `in the future` means that the same order may not result in some future run regardless of results today. I only referenced `SQL table` because they default as REUSEDLT(*YES). The OP should know that order is determined by ORDER BY if it's important. I'd only suggest you edit your answer to mention it. — user2338816, Jul 06 '16 at 04:53

DB2 Concatenate rows

2 Answers2