query question...

[alanchinese]

hi, not sure if db2 has a aggregate function to 'sum (concat)' a string
column in a group by?
i have a table like this:
myCategory, mySubCategory, myGroup, mySubGroup, myString
CatKey1, SubCatKey1, GrpKey1, SubGrpKey1, 'string1'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey2, 'string2'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey2, 'string3'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey1, 'string4'
.......

i want to group CatKye, SubCatKey, GrpKey, SubGrpKey
so, the result set to be like this (in sorted order):
myCategory, mySubCategory, myGroup, mySubGroup, myString
CatKey1, SubCatKey1, GrpKey1, SubGrpKey1, 'string1,string4'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey2, 'string2,string3'

any idea? i heard there is something like ROWCONCAT... didn't find it
in db2 reference though.
thanks a lot.

[Serge Rielau]

alanchinese wrote:

hi, not sure if db2 has a aggregate function to 'sum (concat)' a string
column in a group by?
i have a table like this:
myCategory, mySubCategory, myGroup, mySubGroup, myString
CatKey1, SubCatKey1, GrpKey1, SubGrpKey1, 'string1'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey2, 'string2'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey2, 'string3'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey1, 'string4'
......

i want to group CatKye, SubCatKey, GrpKey, SubGrpKey
so, the result set to be like this (in sorted order):
myCategory, mySubCategory, myGroup, mySubGroup, myString
CatKey1, SubCatKey1, GrpKey1, SubGrpKey1, 'string1,string4'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey2, 'string2,string3'

any idea? i heard there is something like ROWCONCAT... didn't find it
in db2 reference though.
thanks a lot.

CREATE TABLE Employee(name VARCHAR(15),
dept VARCHAR(15));

Name Dept
----- ------
Miso Solutions
John Development
Serge Solutions
Lee L3
Mark ID
Jack L3
Lily Quality
Berni Solutions

SELECT Dept,
SUBSTR(Names, 1, LENGTH(names) -1)
FROM (SELECT
Dept,
REPLACE
(REPLACE
(XMLSERIALIZE
(CONTENT XMLAGG(XMLELEMENT(NAME a, name)
ORDER BY name)
AS VARCHAR(60)), '<A>', ''), '</A>', ',') AS Names
FROM Employee GROUP BY Dept) AS X;

Dept Names
----- --------------------
Solutions Berni, Miso, Serge
Development John
L3 Jack, Lee
ID Mark
Quality Lily
IDUG Europe Presentation C04 "SQL on Fire!" last Tuesday
There will be a repeat at the IOD Conference a week from now.
Where are you located?

Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[alanchinese]

wow, that sounds REALLY compliated.
is there easier solutions? but thanks for the direction...
i am in bay area SF.

Serge Rielau wrote:

alanchinese wrote:

hi, not sure if db2 has a aggregate function to 'sum (concat)' a string
column in a group by?
i have a table like this:
myCategory, mySubCategory, myGroup, mySubGroup, myString
CatKey1, SubCatKey1, GrpKey1, SubGrpKey1, 'string1'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey2, 'string2'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey2, 'string3'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey1, 'string4'
......

i want to group CatKye, SubCatKey, GrpKey, SubGrpKey
so, the result set to be like this (in sorted order):
myCategory, mySubCategory, myGroup, mySubGroup, myString
CatKey1, SubCatKey1, GrpKey1, SubGrpKey1, 'string1,string4'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey2, 'string2,string3'

any idea? i heard there is something like ROWCONCAT... didn't find it
in db2 reference though.
thanks a lot.

CREATE TABLE Employee(name VARCHAR(15),
dept VARCHAR(15));

Name Dept
----- ------
Miso Solutions
John Development
Serge Solutions
Lee L3
Mark ID
Jack L3
Lily Quality
Berni Solutions

SELECT Dept,
SUBSTR(Names, 1, LENGTH(names) -1)
FROM (SELECT
Dept,
REPLACE
(REPLACE
(XMLSERIALIZE
(CONTENT XMLAGG(XMLELEMENT(NAME a, name)
ORDER BY name)
AS VARCHAR(60)), '<A>', ''), '</A>', ',') AS Names
FROM Employee GROUP BY Dept) AS X;

Dept Names
----- --------------------
Solutions Berni, Miso, Serge
Development John
L3 Jack, Lee
ID Mark
Quality Lily
IDUG Europe Presentation C04 "SQL on Fire!" last Tuesday
There will be a repeat at the IOD Conference a week from now.
Where are you located?

Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Serge Rielau]

alanchinese wrote:

wow, that sounds REALLY compliated.
is there easier solutions? but thanks for the direction...
i am in bay area SF.

Then IOD is just a drive away. I've still got seats :-)
This is both the easiest and fastest way.
You could do recursion, but that's slower.
It's not complex, really.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[--CELKO--]

What you are looking for exists in Sybase as LIST() and one of the open
source products (Posttgres? I cannot remember). But that is not the
real question. Why do you wish to destroy First Normal Form (1NF) with
a concatendated list structure? It is the foundation of RDBMS, after
all.

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.

Yes, there are kludges in SQL to do this. You can also still write
procedural code with GOTOs and get "spaghetti code", but it does not
mean you should.

[P Adhia]

alanchinese wrote:

wow, that sounds REALLY compliated.

If you are familiar with recursive queries, you might find following
example easier to understand; but it's still quite a bit of code.
Essentially, it start with the "first" value and keeps appending "next"
value for remaining rows and you select the "last" value. (In the
following example first, next and last are arbitrary)

create table test(c1 varchar(128) not null);
insert into test values('One'), ('Two'), ('Three'), ('Four');

with t(r, c1) as
( select row_number() over() r
, c1
from test
)

, q(r, c1) as
( select r
, c1
from t
where r = 1
union all
select q.r + 1
, q.c1 || ',' || t.c1
from q q, t t
where t.r = q.r + 1
)

select c1
from q
where r = (select max(r) from q);

You can generalize above example to create aggregate/column function for
any scalar function or operator.

P Adhia

[Knut Stolze]

--CELKO-- wrote:

Yes, there are kludges in SQL to do this. You can also still write
procedural code with GOTOs and get "spaghetti code", but it does not
mean you should.

GOTO is actually quite useful _if used appropriately_. For example, it
gives you a nice way in C code to have a single place in a function that
does cleanup.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[--CELKO--]

>GOTO is actually quite useful _if used appropriately_. For example, it gives you a nice way in C code to have a single place in a function that does cleanup. <<

In a low level language like C you can get forced into a corner.
Modern, higher level languages use the TRY.. CATCH from OO languages or
the WHENEVER.. DO model from SQL/PSM and ADA for error handling and get
rid of the GOTO completely.

The best reasons for getting rid of the GOTO are correctness proofs
(Zohar Manna's book is back in print from Dover Publications) and
optimization. My favorite was BLISS, which had no GOTOs and could
consistently beat the best assembly programmers at CMU.

[Knut Stolze]

--CELKO-- wrote:

>>GOTO is actually quite useful _if used appropriately_. For example, it
gives you a nice way in C code to have a single place in a function that
does cleanup. <<

In a low level language like C you can get forced into a corner.
Modern, higher level languages use the TRY.. CATCH from OO languages or
the WHENEVER.. DO model from SQL/PSM and ADA for error handling and get
rid of the GOTO completely.

The best reasons for getting rid of the GOTO are correctness proofs
(Zohar Manna's book is back in print from Dover Publications) and
optimization. My favorite was BLISS, which had no GOTOs and could
consistently beat the best assembly programmers at CMU.

I think you missed my point completely. Most concepts were introduced for a
certain reason and many are still appropriate today in the right
environment - so is concatenation/denormalization of data in SQL
statements.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany