Jacinle Young <ja***********@hongkong.com> wrote:
Hi
If I have a table with two colum ITEM and NAME, like the following
ITEM NAME
======= ============
Apple Nancy
Orange Nancy
Lemon Margaret
Orange Margaret
Banana Tom
and I want to have a result like this
Apple Nancy
Orange Nancy, Margaret
Lemon Margaret
Banana Tom
Can I do it in SQL?
Either you use an external function to do your aggregation, then you might
want to have a look here:
http://www7b.boulder.ibm.com/dmdd/li...309stolze.html
Or you do the string concatenation in SQL using a recursive query. This
would be similar to parsing the string into its pieces, which is described
here:
http://www7b.boulder.ibm.com/dmdd/li...03stolze1.html
Your query could look like this (untested):
WITH t1(rowNum, item, name) AS
( SELECT rownumber() over(), item, name
FROM yourTable
GROUP BY item ),
t2(item, list, cnt) AS
( SELECT item, name, 1
FROM t1
WHERE rowNum = 1
UNION ALL
SELECT t2.item, list || ', ' || name, cnt + 1
FREM t2, t1
WHERE t2.item = t1.item AND
t2.cnt + 1 = t1.rowNum )
SELECT item, list
FROM t2
WHERE ( item, cnt ) IN ( SELECT item, MAX(rowNum)
FROM t1
GROUP BY item )
--
Knut Stolze
Information Integration
IBM Germany / University of Jena