nek <nekiv90hotmail.com> wrote:

You could use recursive SQL like this:> 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.

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

## Bookmarks