Concatenate column values from multiple rows

[nek]

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.

[Knut Stolze]

nek <nekiv90@hotmail.com> wrote:

> 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.

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".

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[nek]

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.


Knut Stolze <stolze@de.ibm.com> wrote in message news:<bk6lba$rl9$1@fsuj29.rz.uni-jena.de>...

> nek <nekiv90@hotmail.com> wrote:
>

> > 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.

>
> 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".

[Knut Stolze]

nek <nekiv90@hotmail.com> wrote:

> 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.

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

--
Knut Stolze
Information Integration
IBM Germany / University of Jena