Re: Spaltenergebnisse in einer Zeile
[ ruban.de ] [ Antworten ] [ Forum ]
Geschrieben von Markus am September 19, 2003 um 06:49:
Als Antwort auf Re: Spaltenergebnisse in einer Zeile geschrieben von Frd on September 17, 2003 um 09:10:
Alle Beiträge des Diskussionsthemas
Beitrag 1 aus der Diskussionsgruppe
Von:nek (nekiv90@hotmail.com)
Betrifft:Concatenate column values from multiple rows
View this article only
Newsgroups:comp.databases.ibm-db2
Datum:2003-09-15 21:24:25 PST
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.
Folgetext zu diesem Beitrag schreiben
Beitrag 2 aus der Diskussionsgruppe
Von:Knut Stolze (stolze@de.ibm.com)
Betrifft:Re: Concatenate column values from multiple rows
View this article only
Newsgroups:comp.databases.ibm-db2
Datum:2003-09-16 02:32:27 PST
nek 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 wrote in message news:...
> nek 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 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".
Folgetext zu diesem Beitrag schreiben
Schreibe eine Antwort
|