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

Aggregating strings

P: n/a
Hi folks,

One the more frequently asked questions is how to "sum" up strings by
virtue of concatenating them in an aggregate function.
Her eis a rather simpel solution that may be worth sharing (you'll be
teh judge):
DROP TABLE T;
CREATE TABLE T(servername VARCHAR(15), volume VARCHAR(10), capacity
VARCHAR(10));
INSERT INTO T VALUES
('ServerA', 'Vol1', '5GB'),
('ServerB', 'Vol1', '5GB'),
('ServerC', 'Vol2', '11GB'),
('ServerD', 'Vol2', '11GB'),
('ServerE', 'Vol3', '20GB');

SELECT Volume, Capacity,
VARCHAR(REPLACE(REPLACE(VARCHAR(XML2CLOB(XMLAGG(XM LELEMENT(NAME
a, ServerName)
ORDER BY ServerName)), 60),
'<A>', ''),
'</A>', ','), 60) AS ServerList
FROM T GROUP BY Volume, Capacity;

VOLUME CAPACITY SERVERLIST
---------- ---------- ------------------
Vol1 5GB ServerA,ServerB,
Vol2 11GB ServerC,ServerD,
Vol3 20GB ServerE,

3 record(s) selected.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 24 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Serge Rielau wrote:
Hi folks,

One the more frequently asked questions is how to "sum" up strings by
virtue of concatenating them in an aggregate function.
Her eis a rather simpel solution that may be worth sharing (you'll be
teh judge):
DROP TABLE T;
CREATE TABLE T(servername VARCHAR(15), volume VARCHAR(10), capacity
VARCHAR(10));
INSERT INTO T VALUES
('ServerA', 'Vol1', '5GB'),
('ServerB', 'Vol1', '5GB'),
('ServerC', 'Vol2', '11GB'),
('ServerD', 'Vol2', '11GB'),
('ServerE', 'Vol3', '20GB');

SELECT Volume, Capacity,
VARCHAR(REPLACE(REPLACE(VARCHAR(XML2CLOB(XMLAGG(XM LELEMENT(NAME
a, ServerName)
ORDER BY ServerName)), 60),
'<A>', ''),
'</A>', ','), 60) AS ServerList
FROM T GROUP BY Volume, Capacity;

VOLUME CAPACITY SERVERLIST
---------- ---------- ------------------
Vol1 5GB ServerA,ServerB,
Vol2 11GB ServerC,ServerD,
Vol3 20GB ServerE,

3 record(s) selected.


Thanks a lot for a solution to a problem that has been worrying us for a
while.

What level of DB2 did the XML functions appear?

Can this be wrapped in a function with the argument 'ServerName' (I
don't see it but you are more expert here)?

Is there any easy way to remove the trailing comma? This would be easy
if SUBSTR understood negative start and length parameters like PHP.
(Hint, hint)

The following formatting may be a little easier to comprehend (if I've
understood it myself):

SELECT Volume, Capacity,
VARCHAR(
REPLACE(
REPLACE(
VARCHAR(
XML2CLOB(
XMLAGG(
XMLELEMENT(NAME a, ServerName)
ORDER BY ServerName)
),
60),
'<A>', ''),
'</A>', ','),
60) AS ServerList
Feb 24 '06 #2

P: n/a
DB2 V8.1 GA for LUW I think (at least there is no change marker on the
docs):
http://publib.boulder.ibm.com/infoce...n/r0000736.htm

You can't wrap aggrehgates into functions. They need the context of
their group. (Same for MAX, MIN, etc...)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 24 '06 #3

P: n/a
Wow, that's another good one. I'm not even sure i knew of the XML
FUNCTIONs. I do find it odd that it's listed under Expressions though.

Just last week i was working on something that could use this, and
ended up implementing a FOR LOOP. This may replace that on the next
iteration.

B.

Feb 27 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.