data from several rows in one field

[Benjamin Sch?neck]

Hi,
I'm looking for a possebility to concat data from several rows of one
table into one field.

Example Table:

No. Name
------------------
1 Peter
1 Alec
1 Jane
2 Annie
2 Louis

I'd like to receive a result like this:

1 Peter, Alec, Jane
2 Annie, Louis

Is it possible to manage this using one SQL-Statement?

Any help is appreciated.
Thanks in advance
Benjamin
using IBM DB2 7.2

[Lee Dilworth]

you need to use recursive SQL todo this....Knut posted a good example of
this in action..easily adapted to return your desired result, see this
thread:

http://groups.google.com/groups?hl=e...readm=bl15kd%2
41uj%241%40fsuj29.rz.uni-jena.de&rnum=2&prev=/groups%3Fq%3Dconcatenate%2Brow
s%2Bgroup:comp.databases.ibm-db2%2Bgroup:comp.databases.ibm-db2%2Bgroup:comp
..databases.ibm-db2%2Bgroup:comp.databases.ibm-db2%2Bgroup:comp.databases.ibm
-db2%26hl%3Den%26lr%3D%26ie%3DUTF-8%26safe%3Doff%26scoring%3Dd%26selm%3Dbl15
kd%25241uj%25241%2540fsuj29.rz.uni-jena.de%26rnum%3D2

"Benjamin Sch?neck" <bs********@gmx.de> wrote in message
news:37**************************@posting.google.c om...

Hi,
I'm looking for a possebility to concat data from several rows of one
table into one field.

Example Table:

No. Name
------------------
1 Peter
1 Alec
1 Jane
2 Annie
2 Louis

I'd like to receive a result like this:

1 Peter, Alec, Jane
2 Annie, Louis

Is it possible to manage this using one SQL-Statement?

Any help is appreciated.
Thanks in advance
Benjamin
using IBM DB2 7.2

[Benjamin Schöneck]

Thanks a million. It was exactly what I needed!
Works out perfectly!!!
"Lee Dilworth" <le****************@hotmail.com> schrieb im Newsbeitrag
news:br*************@ID-118488.news.uni-berlin.de...

you need to use recursive SQL todo this....Knut posted a good example of
this in action..easily adapted to return your desired result, see this
thread:

http://groups.google.com/groups?hl=e...readm=bl15kd%2 41uj%241%40fsuj29.rz.uni-jena.de&rnum=2&prev=/groups%3Fq%3Dconcatenate%2Brow s%2Bgroup:comp.databases.ibm-db2%2Bgroup:comp.databases.ibm-db2%2Bgroup:comp ..databases.ibm-db2%2Bgroup:comp.databases.ibm-db2%2Bgroup:comp.databases.ibm -db2%26hl%3Den%26lr%3D%26ie%3DUTF-8%26safe%3Doff%26scoring%3Dd%26selm%3Dbl 15 kd%25241uj%25241%2540fsuj29.rz.uni-jena.de%26rnum%3D2

"Benjamin Sch?neck" <bs********@gmx.de> wrote in message
news:37**************************@posting.google.c om...

Hi,
I'm looking for a possebility to concat data from several rows of one
table into one field.

Example Table:

No. Name
------------------
1 Peter
1 Alec
1 Jane
2 Annie
2 Louis

I'd like to receive a result like this:

1 Peter, Alec, Jane
2 Annie, Louis

Is it possible to manage this using one SQL-Statement?

Any help is appreciated.
Thanks in advance
Benjamin
using IBM DB2 7.2

[Evan Smith]

I've found myself needing this functionality twice for distinct
reasons in the last month. I had used both an original recursive SQL
and used Knut's methodology to get the results that I wanted.

I was thinking that this sort of looks like something that could
possibly written as a column function, much like SUM, or AVG, except
that it would operate on text instead of numerics. Does it seem
reasonable that this could be done as an SQL function or a UDF? Before
I started committing my free hours to such a project, I thought I
would check if any of the database gurus out here had ever
contemplated it and rejected it because of its complexity or
unfeasibility.

Evan

"Benjamin Schöneck" <be****************@dialog-edv.de> wrote in message news:<br*************@news.t-online.com>...

Thanks a million. It was exactly what I needed!
Works out perfectly!!!
"Lee Dilworth" <le****************@hotmail.com> schrieb im Newsbeitrag
news:br*************@ID-118488.news.uni-berlin.de...

you need to use recursive SQL todo this....Knut posted a good example of
this in action..easily adapted to return your desired result, see this
thread:

http://groups.google.com/groups?hl=e...readm=bl15kd%2

41uj%241%40fsuj29.rz.uni-jena.de&rnum=2&prev=/groups%3Fq%3Dconcatenate%2Brow

s%2Bgroup:comp.databases.ibm-db2%2Bgroup:comp.databases.ibm-db2%2Bgroup:comp

.databases.ibm-db2%2Bgroup:comp.databases.ibm-db2%2Bgroup:comp.databases.ibm

-db2%26hl%3Den%26lr%3D%26ie%3DUTF-8%26safe%3Doff%26scoring%3Dd%26selm%3Dbl

15

[Christian Maslen]

> I was thinking that this sort of looks like something that could

possibly written as a column function, much like SUM, or AVG, except
that it would operate on text instead of numerics. Does it seem
reasonable that this could be done as an SQL function or a UDF? Before
I started committing my free hours to such a project, I thought I
would check if any of the database gurus out here had ever
contemplated it and rejected it because of its complexity or
unfeasibility.

SQL Server are offering a PIVOT operator (along with UNPIVOT to do the
opposite) to acheive this in the Yukon release.
eg:
SELECT *
FROM ItemAttributes AS ATR
PIVOT
(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
WHERE itemid IN(5,6)

I've not seen any mention of it in any existing or proposed ANSI/SQL
standard. I'd find the feature useful for generating spreadsheet
output, but the recursive method works well enough.

See the following link for details:
http://msdn.microsoft.com/library/de...SQLEnhance.asp

Christian.

[Serge Rielau]

What Benjamin needs here is a user defined aggreate.
Informix IDS has this technology and it is being looked at as a
requirements. Too early to comment on a ship vehicle though.
User defined aggregates can be simulated with scratchpad UDF to some degree.
But again, recursion usually does the job as well and works well enough.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[AK]

Knut Stolze has recently published a great article on customized sums on DB2DD.
I think oyu could try the same approach, a UDF with a scratchpad.

[AK]

Hi Serge,

the way I'm reading execution plans, a cursor seems to be open for
every iteration of a recursive query, and a temporary result set is
opened (and later discarded) once per every iteration. The performance
of recursive queries seems to depend more on number of iterations than
on anything else.

Please correct me if I'm wrong.

[Serge Rielau]

You are correct that there is a cursor, but it stays open (it gets
secondary EOFs and can recover from them The TEMP stays. It just
accumulates. I think it's called semi-naive recursion in the literature.
(I by contrast am naive and cursing)
Rows
RETURN
( 1)
Cost
I/O
|
334.333
TBSCAN
( 2)
173.39
0
|
334.333
TEMP
( 3)
140.897
0
|
334.333
UNION
( 4)
118.422
0
/----+---\
333.333 1
TBSCAN TBSCAN
( 5) ( 6)
112.466 0.0048
0 0
| |
334.333 1
TEMP TABFNC: SYSIBM
( 3) GENROW
140.897
0

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab