Concatenate column values from multiple rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nek - 16 Sep 2003 05:24 GMT

Greetings,

Would it be possible to construct SQL to concatenate column values
from multiple rows?

SELECT ... FROM T1, T2 WHERE T1.key=T2.fkey (group by key?);

The following is an example:

Table 1 (key and other columns):
key
---
A
B
C

Table 2 (fkey, col1 etc.):
fkey col1
---- ----
A 1
A 2
A 3
B 1
B 2
C 4

The SQL to be constructed should return ALL col1 values concatenated
for the same key column:

key con-col1
--- --------
A 123
B 12
C 4

Any hints or suggestions would be greatly appreciated.

Platform is DB2 V8 on W2K + FP2.

Reply to this Message

Knut Stolze - 16 Sep 2003 10:32 GMT

> Greetings,
>
[quoted text clipped - 32 lines]
>
> Any hints or suggestions would be greatly appreciated.

You could use recursive SQL like this:

WITH x(key, val, rnum) AS
( SELECT fkey, col1, row_number() over(partition by fkey)
FROM myTable ),
y(key, str, cnt, cnt_max) AS
( SELECT key, VARCHAR('', 1000), 0, MAX(rnum)
FROM x
GROUP BY key
UNION ALL
SELECT y.key, y.str || RTRIM(CHAR(x.val)), y.cnt + 1, y.cnt_max
FROM x, y
WHERE x.key = y.key AND
x.rnum = y.cnt + 1 AND
y.cnt < y.cnt_max )
SELECT key, str
FROM y
WHERE y.cnt = y.cnt_max;

"myTable" corresponds to the second table you listed above. If you want to
restrict the result in some way, you only have to modify the common table
expression named "x".

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Reply to this Message

nek - 18 Sep 2003 06:38 GMT

Thanks Knut! It returned what was expected.

One problem is its reponse time. Here is the result of running it in
db2batch:

Stats:
(1) MyTable: 80,000 rows of 97 bytes record length
(2) x: 15,000 rows of 85 bytes record length

Elapsed Time (s) 2914.922
Rows fetched: 5752
Rows printed: 5752

Arith. mean: 2914.922
Geom. mean: 2914.922

Not really optimal considering these tables being two of the six
tables joined in the query, indexed and clustered on 'key' and 'fkey'
respectively. The query was run after reorg.

Best Regards,
Nek.

> > Greetings,
> >
[quoted text clipped - 55 lines]
> restrict the result in some way, you only have to modify the common table
> expression named "x".

Reply to this Message

Knut Stolze - 22 Sep 2003 08:48 GMT

> One problem is its reponse time. Here is the result of running it in
> db2batch:
[quoted text clipped - 13 lines]
> tables joined in the query, indexed and clustered on 'key' and 'fkey'
> respectively. The query was run after reorg.

Maybe using aggregate functions (where the aggregation is actually the
concatenation) would be better performing:
http://www7b.boulder.ibm.com/dmdd/library/techarticle/0309stolze/0309stolze.html

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Reply to this Message

nek - 26 Sep 2003 01:16 GMT

Thanks Knut.

Yeah, I tried to look for solution like 'select key, getaggregate(key)
..from..'
where getaggregate(key) returns the concatenation of columns discussed
before.

Admittedly it is a table design flaw. The temporary solution is to
pass all rows returned for the key to reporting tools such as Business
Objects or Crystal Reports where they can be massaged.

I'll experiment the aggregate option you suggested, hopefully with
better response time.

Best Regards,
Nek.

> > One problem is its reponse time. Here is the result of running it in
> > db2batch:
[quoted text clipped - 17 lines]
> concatenation) would be better performing:
> http://www7b.boulder.ibm.com/dmdd/library/techarticle/0309stolze/0309stolze.html

Reply to this Message

Knut Stolze - 26 Sep 2003 11:49 GMT

> Thanks Knut.
>
> Yeah, I tried to look for solution like 'select key, getaggregate(key)
> ..from..'
> where getaggregate(key) returns the concatenation of columns discussed
> before.

Some other thought:
(+) indexes with include columns to avoid table scans
(+) generated columns
(+) the usual performance evaluation and improvement techniques, starting
with analysing the access plan

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Reply to this Message

TD - 01 Mar 2005 03:09 GMT

I borrowed this very useful piece of code and modified it for my table
definitions. It works very well with the exception of the warning of a
possible infinite loop in the Y table as defined. Is there any way this can
be removed ? The max common values for each record I have in my table 2 [or
second table] is say 10, that being there are a max number of 10 records to
concat but they vary from 1-10. I am also concating with a '+' between each
value but is always adds a '+' to the final value as well..

Further I am restricting the X table to a single FKEY value and using it as
a UDF I can call from a query or SQL SP.

Any suggestions would be greatly appreciated.

TD

> You could use recursive SQL like this:
>
[quoted text clipped - 18 lines]
> restrict the result in some way, you only have to modify the common table
> expression named "x".

Reply to this Message

Knut Stolze - 07 Mar 2005 10:30 GMT

> I borrowed this very useful piece of code and modified it for my table
> definitions. It works very well with the exception of the warning of a
[quoted text clipped - 6 lines]
> Further I am restricting the X table to a single FKEY value and using it
> as a UDF I can call from a query or SQL SP.

I already answered that ony off-line 'cause I didn't knew that you
cross-posted this to the NG as well.

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Reply to this Message