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]

Re: data from several rows in one field

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=en&lr=&ie=UTF-8&safe=off&threadm=bl15kd%2

41uj%241%40fsuj 29.rz.uni-jena.de&rnum=2& prev=/groups%3Fq%3Dco ncatenate%2Brow
s%2Bgroup:comp. databases.ibm-db2%2Bgroup:com p.databases.ibm-db2%2Bgroup:com p
..databases.ibm-db2%2Bgroup:com p.databases.ibm-db2%2Bgroup:com p.databases.ibm
-db2%26hl%3Den%2 6lr%3D%26ie%3DU TF-8%26safe%3Doff% 26scoring%3Dd%2 6selm%3Dbl15
kd%25241uj%2524 1%2540fsuj29.rz .uni-jena.de%26rnum% 3D2

"Benjamin Sch?neck" <bschoeneck@gmx .de> wrote in message
news:37c41652.0 312080804.21ec2 a4b@posting.goo gle.com...[color=blue]
> 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[/color]

[Benjamin Schöneck]

Re: data from several rows in one field

Thanks a million. It was exactly what I needed!
Works out perfectly!!!


"Lee Dilworth" <lee_dilworthno spam@hotmail.co m> schrieb im Newsbeitrag
news:br2i1h$27b nk8$1@ID-118488.news.uni-berlin.de...[color=blue]
> 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:
>
>[/color]
http://groups.google.com/groups?hl=e...readm=bl15kd%2[color=blue]
>[/color]
41uj%241%40fsuj 29.rz.uni-jena.de&rnum=2& prev=/groups%3Fq%3Dco ncatenate%2Brow[color=blue]
>[/color]
s%2Bgroup:comp. databases.ibm-db2%2Bgroup:com p.databases.ibm-db2%2Bgroup:com p[color=blue]
>[/color]
..databases.ibm-db2%2Bgroup:com p.databases.ibm-db2%2Bgroup:com p.databases.ibm[color=blue]
> -db2%26hl%3Den%2 6lr%3D%26ie%3DU TF-8%26safe%3Doff% 26scoring%3Dd%2 6selm%3Dbl[/color]
15[color=blue]
> kd%25241uj%2524 1%2540fsuj29.rz .uni-jena.de%26rnum% 3D2
>
> "Benjamin Sch?neck" <bschoeneck@gmx .de> wrote in message
> news:37c41652.0 312080804.21ec2 a4b@posting.goo gle.com...[color=green]
> > 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[/color]
>
>[/color]

[Evan Smith]

Re: data from several rows in one field

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" <benjamin.schoe neck@dialog-edv.de> wrote in message news:<br3tmv$mf 3$04$1@news.t-online.com>...[color=blue]
> Thanks a million. It was exactly what I needed!
> Works out perfectly!!!
>
>
> "Lee Dilworth" <lee_dilworthno spam@hotmail.co m> schrieb im Newsbeitrag
> news:br2i1h$27b nk8$1@ID-118488.news.uni-berlin.de...[color=green]
> > 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:
> >
> >[/color]
> http://groups.google.com/groups?hl=e...readm=bl15kd%2[color=green]
> >[/color]
> 41uj%241%40fsuj 29.rz.uni-jena.de&rnum=2& prev=/groups%3Fq%3Dco ncatenate%2Brow[color=green]
> >[/color]
> s%2Bgroup:comp. databases.ibm-db2%2Bgroup:com p.databases.ibm-db2%2Bgroup:com p[color=green]
> >[/color]
> .databases.ibm-db2%2Bgroup:com p.databases.ibm-db2%2Bgroup:com p.databases.ibm[color=green]
> > -db2%26hl%3Den%2 6lr%3D%26ie%3DU TF-8%26safe%3Doff% 26scoring%3Dd%2 6selm%3Dbl[/color]
> 15[/color]

[Christian Maslen]

Re: data from several rows in one field

> I was thinking that this sort of looks like something that could[color=blue]
> 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.[/color]

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/default.asp?url=/library/en-us/dnsql90/html/sql_YukonTSQLEnhance.asp

Christian.

[Serge Rielau]

Re: data from several rows in one field

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]

Re: data from several rows in one field

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]

Re: data from several rows in one field

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]

Re: data from several rows in one field

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