recursive sql / concatenating multiple rows into a single row

[Juergen_G]

Hi DB400 Gurus!

concatenating multiple rows into a single row - with recursive sql

Is it possible on DB400?

If yes - please post a sql script which use recursive sql.

PS - I have found a list with solutions for MS SQL Server and .net :
http://www.simple-talk.com/sql/t-sql...-transact-sql/

thanks in Advance
Jürgen

[K2r400]

Re: recursive sql / concatenating multiple rows into a single row

Is it possible on DB400?

Yes it's possible with V5R4.

An example :

WITH salarieb (matricule, nom, niveau) AS
(SELECT matricule, nom, 1 FROM salarie WHERE nom = 'JOEL'
UNION ALL
SELECT a.matricule, a.nom, niveau +1 FROM salarie a,
salarieb b WHERE b.matricule = a.boss )
SELECT nom, niveau FROM salarieb

Post different rows of a table, an give me the result expected, I shall give you the SQL statement.

[Juergen_G]

Re: recursive sql / concatenating multiple rows into a single row

Yes it's possible with V5R4.
Post different rows of a table, an give me the result expected, I shall give you the SQL statement.

Hi K2r400,
I would need a sql statement for a this:

Name Fruit
---------------
Joe Apple
Joe Bananna
Joe Pineapple
Karl Kiwi
Karl Apple


Result:
Joe Apple, Bananna, Pineaplle
Karl Kiwi, Apple

Thank you for you help
Jürgen

[B.Hauser]

Re: recursive sql / concatenating multiple rows into a single row

To solve this problem you need to create a view for the recursive CTE and write an UDF that concats the names, much like in the examples on the website you posted.

... more details, if I've some more time.

Birgitta

[K2r400]

Re: recursive sql / concatenating multiple rows into a single row

The easy way to resolve the problem is an UDF, but you can do it with recursive too :

with t1 as (SELECT name,                                           
                   fruit,                                          
            ROW_NUMBER() over(partition by name ORDER BY fruit) niv
            FROM mytable),                                         
     t2    (tname, tFruit, tniv) as (                              
            select Name, Fruit, niv from t1 where niv = 1          
           union all                                               
            select Name,                                           
                   trim(tFruit) concat ', ' concat trim(Fruit),     
                   tNiv + 1                                        
            from t1, t2                                            
            where (name, niv) = (tname, tniv +1)),                 
 t3 as (select tName, Max(tNiv) tniv from t2 group by tName)       
select T2.tName, tFruit from t2 join t3                            
    on t2.tname = t3.tname                                         
   and t2.tniv  = t3.tniv

The result of this query give you :

TNAME       TFRUIT                 
Karl        Apple, Kiwi             
Joe         Apple, Banana, Pineapple 
********  Fin de données  ********

[Vectorspace]

I was looking for how to aggregate strings from multiple rows into one, and found this thread. I thought I would resurrect the thread for two reasons.

First, there is now an aggregate function to do this, ListAgg(), eliminating the need for a recursive CTE:

with mytable (Name,Fruit) as (
    values('Karl','Apple'),
          ('Karl','Kiwi'),
          ('Joe','Apple'),
          ('Joe','Banana'),
          ('Joe','Pineapple'))

select Name, listagg(fruit,', ') within group (order by Fruit) as fruit
  from mytable
 group by Name

Second, I spent quite a while trying to understand how and why the recursive CTE worked (I don't like using code if I don't understand it), and I figured I would share that for to anyone who does need to use a recursive CTE.

The way recursive CTE's work, is in iterations. Each time the SQL processor runs the CTE, it is based on the rows returned by the previous run. On first run (when there is no previous run), it takes that to mean the previous run returned zero rows.

Here is a slightly cleaned up version of the SQL in K2r400's post:

-- This is the raw data
with mytable (Name,Fruit) as (
    values('Karl','Apple'),
          ('Karl','Kiwi'),
          ('Joe','Apple'),
          ('Joe','Banana'),
          ('Joe','Pineapple')),

-- This adds a row number for each fruit within each name
t1 as (
  select Name, Fruit,                                          
         row_number() over(partition by Name order by Fruit) RowNum
    from mytable), 

-- This is the recursive CTE
t2 (tName, tFruit, tRowNum) as (                              
  select Name, Fruit, RowNum
    from t1
   where RowNum = 1          
   union all                                               
   select Name,                                           
          trim(tFruit) || ', ' || trim(Fruit),     
          tRowNum + 1                                        
     from t1
    inner join t2                                            
       on(Name, RowNum-1) = (tName, tRowNum)),

-- This takes the final output from the recursive CTE, and adds a new row number (for each fruit within each name), but descending this time. So row number 1 is the last row for each name
t3 as (
  select tName, tFruit,
         row_number() over(partition by tName order by tRowNum desc) RowNumD
    from t2)

-- This selects only the last row (according to the row number added in t3) for each name
select tName, tFruit
  from t3
 where RowNumD = 1;

CTE t2 is the iterative CTE. Each iteration returns:
t1 row 1 (RowNum = 1)
plus (union all)
select all t1 rows where the previous row (by row number RowNum) was returned by the previous iteration (t2).
(this is an inner join, these rows concatenate the fruit name with that of the previous row, that is how the list of fruits is built)

To work through it for the Joe entries:

Iteration 1
Assume previous iteration (iteration 0) returned zero rows
Therefore this iteration returns row 1 (Apple) only
Return
row 1 (Apple)

Iteration 2
Return
row 1 (Apple)
row 2 (row1 + Banana, = Apple + Banana) (because previous row is row1, which is in previous iteration results)

Iteration 3
Return
row 1 (Apple)
row 2 (row1 + Banana, = Apple + Banana) (because previous row is row1, which is in previous iteration results)
row 3 (row1 +Kiwi2, = Apple + Banana + Kiwi) (because previous row is row2, which is in previous iteration results)

Iteration 4
Return
row 1 (Apple)
row 2 (row1 + Banana, = Apple + Banana) (because previous row is row1, which is in previous iteration results)
row 3 (row1 +Kiwi2, = Apple + Banana + Kiwi) (because previous row is row2, which is in previous iteration results)
There is no row 4 in the table, so no additional rows are returned
No additional rows added since Iteration 3 - stop iteration, this is the final results set

Finally, the t3 CTE adds row numbering where the last row (the one with all the fruits) is row 1. Then the final select selects only rows where this row number = 1, which means only the row with all the fruits is selected.

[Vectorspace]

Typo in the above. The Row 3 lines for iterations 3 and 4 should be:


row 3 (row2 +Kiwi2, = Apple + Banana + Kiwi) (because previous row is row2, which is in previous iteration results)