Hi,

I have the following data being returned:

ID Region
---- ------------
1 northwest
1 southeast
2 midwest


Is it possible to use SQL to return this data as:

ID Region
---- ------------
1 northwest,southeast
2 midwest

Currently I'm using code to loop through and create the second data
structure, but it would be nice if I could have the database take care
of it for me.

Thanks,

Ben

This was asked earlier today.

Brian Tkatch replied:
"Search for XMLAGG() functions in the newsgroups."

Hope that helps.

aj

ben.s.carlson (AT) gmail (DOT) com wrote:

Hi aj,Ben

I cant use Xml function...is it possible using sql

Thanks

Thyis is called violation of First Normal Form and violationof the
basic prinicple of tiered architecture. You are suipposed to be doign
formatting in the front end, not the database. You might want to read a
basic RDBMS book before you start programming.

rAinDeEr wrote:

Yes you can with SQL ... by using one of DB2's XML functions (XMLAGG as
others have pointed out) for something it wasn't meant to be used for
:-).

It's a hack, and it's not obvious because as Celko points out this
doesn't "fit" relational theory. If you search this newsgroup on Google
Groups for "XMLAGG" or "Aggregating Strings" the answer is in a post
fairly high up the results list.

Alternatively, if you meant your version of DB2 is old enough that it
doesn't support the XMLAGG function ... well, you'll have to create an
external UDF to handle this or, better still, take Celko's suggestion
and do it in the application layer.


Dave.

--

Hi,

First of all I seek forgiveness in the name of all RDBMS gods for this
question. But strange requirements in front end always result in this
kind of questions.

A family table with one level of hierarchy

================================================== ===========================
PERSON NAME PARENT NAME
----------------------- -----------------------
JACK NULL
PETER NULL
JOHN JACK
ANNIE JACK
THOMAS NULL
TOM THOMAS
================================================== ===========================
The front end requirement states that two record should be shown per
page as a single column with (parent name alone if no children is
present) or (parent name,child names... if children is present). Front
end formatting is also nice but the pagination sorts of mandates to get
the children seperately and add them to the parents shown in the page.
Its just a matter of executing one more query but still the existence
of column functions like sum,max.min etc makes me think if there is
column function like CONCAT_ROWS() which will just return rows as a
single column.

================================================== ===========================
FAMILY DATA
----------------------
JACK,ANNIE,JOHN
PETER
================================================== ===========================


Thanks,
rAinDeEr

Hi, Ben

How about a recursive CTE?

CREATE TABLE Foo(
id INT NOT NULL,
descr VARCHAR(15) NOT NULL,
PRIMARY KEY(id, descr));

INSERT INTO Foo(id, descr)
VALUES(1, '1ST.1'),
(1, '1ST.2'),
(2, '2ND.1'),
(2, '2ND.2'),
(2, '2ND.3'),
(2, '2ND.4'),
(3, '3RD.1');

WITH F(id, cur_min_descr, max_descr, joined_descr) AS(
SELECT id, MIN(descr), MAX(descr), CAST(MIN(descr) AS VARCHAR(1600))
FROM Foo
GROUP BY id
UNION ALL
SELECT Foo.id, Foo.descr, F.max_descr, F.joined_descr || '||' ||
Foo.descr
FROM Foo, F
WHERE Foo.id = F.id
AND Foo.descr > F.cur_min_descr
AND NOT EXISTS(SELECT *
FROM Foo AS F2
WHERE F2.id = Foo.id
AND F2.descr > F.cur_min_descr
AND F2.descr < Foo.descr)
)
SELECT id, joined_descr
FROM F
WHERE cur_min_descr = max_descr;

DROP TABLE Foo;

---
Andrey Odegov
avodeGOV (AT) yandex (DOT) ru
(remove GOV to respond)