> nek <nekiv90hotmail.com> wrote:

>

Quote:

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