Concatenate Values from Multiple_Rows into One Column

happydb2 · #1 (**permalink**) 09-26-08, 03:01

db2=8.2 FP9A (32 bit)
OS = AIX 5.1

Hi I've seen similar problems to mine floating around the place but i have not been able to adapt their solutions to my problem.

What i have is a badly designed db with Table1:
("NUMBER" CHARACTER(50),
"REC_NO" INTEGER,
"DESCRIPTION" CHARACTER(130)
)

with data like:
NUMBER REC_NO DESCRIPTION
-------------------------------
1000 1 This change will
1000 2 make a difference
1000 3 to the world.
1001 2 Hello to all
1001 3 those who care to
1001 5 read this

Note that the Rec_no may not start from 1 and may not be consecutive but it is sequential.

What i need is to to be able to report the table as:
1000 This change will make a difference to the world
and
1001 Hello to all those who care to read this.

Ideally i would like a function but at the moment getting the SQL correct would be a good start.

Thanks.

yvanroy · #2 (**permalink**) 09-26-08, 08:19

Using previous threads as samples, I came up with the following:

with
-- create sample table
temp (number, rec_no, description) as
( values ('1000',1,'This change will'), ('1000',2,'make a difference'), ('1000',3,'to the world.')
,('1001',2,'Hello to all'), ('1001',3,'those who care to'), ('1001',5,'read this')
)
SELECT number,
VARCHAR(
REPLACE(
REPLACE(
VARCHAR(
XML2CLOB(
XMLAGG(
XMLELEMENT(NAME a, description)
ORDER BY rec_no)
),
600),
'<A>', ''),
'</A>', ' '),
600) AS transpose_col
FROM temp
GROUP BY number
;

happydb2 · #3 (**permalink**) 09-28-08, 19:18

Hi thanks for that but I'm not familiar with the XML expressions... I've never used them before and I'm trying to make sense of the temp table as well.

Is that meant to be 1 statement?

stolze · #4 (**permalink**) 09-28-08, 21:35

Yes, that's just one SQL statement.

As for the common table expression (it's not a temp table!) and the XML functions, it is best to read it up in the manual. The explanation is typically quite good.

Peter.Vanroose · #5 (**permalink**) 09-29-08, 04:03

IMHO, using the XML possibilities of DB2 is overkill, and "unexpected" to say the least. You only want some kind of "aggregate concatenation" (i.e., a concat over the fields of one column in one group), more or less as if you would do aggregate SUM or COUNT.
Only, concat is order-dependent, while SUM and COUNT aren't; this explains why the SQL syntax doesn't have basic syntax for doing this.

In the code library of this forum, there is a contribution on aggregate concatenation using CTEs: see http://www.dbforums.com/showthread.p...30#post6323330

Agreed, this doesn't look simpler than the XML approach, but it "only" needs basic SQL understanding skills, no XML.
Applied to your example:

Code:

WITH t(n, seq, list) AS
( SELECT number, rec_no, CAST(RTRIM(description) AS VARCHAR(32000))
  FROM   Table1
 UNION ALL
  SELECT n, rec_no, list || ' ' ||RTRIM(description)
  FROM   t INNER JOIN Table1 ON n = number
  WHERE  LOCATE(RTRIM(description),list) = 0
    AND  rec_no = (SELECT MIN(rec_no) FROM Table1
                   WHERE  number = t.n
                     AND  rec_no > t.seq)
)
, l(n, len) AS
( SELECT n, MAX(LENGTH(list))
  FROM   t
  GROUP BY n )
SELECT t.n, t.list
FROM   t INNER JOIN l  ON  l.n = t.n AND l.len = LENGTH(t.list)

You may decrease the value "32000", depending on the expected total length of the concatenated "description" column.

Internet Services

Web Development

Digital Marketing

Consumer Tech