SQL DB2 Accumulate Strings from multiple rows into one column

Question

I have two tables header and details.

Header: id + data example 1; Text

Details: id + description example 1 description 1; 1 description 2

I want to select both tables with the following result:

1; description 1 description 2

My idea was using an udf like

BEGIN                                                         
  DECLARE RETURNVAL VARCHAR(1024) NOT NULL DEFAULT '' ;       
  FOR CSRC1 AS C1 CURSOR                                      
     FOR SELECT description
        FROM myTable WHERE ID = PARID            
  END FOR ;                                                   
  RETURN LTRIM(RETURNVAL) ;                                   
END                                                           

But I wonder if this could be done with pure sql which should be much faster.

//Ralf

Answer 1

This is what I used to use before the listagg() function became available.

(select substr(xmlserialize(xmlagg(xmltext(concat(', ', description))) as varchar(1024)),3) from myTable where ID = PARID) as "Concatenated Values"

The xml*() functions have been around much longer than listagg(). See if that works.