By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,282 Members | 1,615 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,282 IT Pros & Developers. It's quick & easy.

query question...

P: n/a
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.

Oct 7 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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/
Oct 7 '06 #2

P: n/a
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/
Oct 8 '06 #3

P: n/a
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/
Oct 8 '06 #4

P: n/a
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.

Oct 8 '06 #5

P: n/a
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
Oct 8 '06 #6

P: n/a
--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
Oct 8 '06 #7

P: n/a
>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.

Oct 9 '06 #8

P: n/a
--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
Oct 10 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.