467,887 Members | 1,557 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,887 developers. It's quick & easy.

Aggregating strings

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
  • viewed: 2863
Share:
3 Replies
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
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
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.

Similar topics

20 posts views Thread by Ravi | last post: by
17 posts views Thread by Gordon Airport | last post: by
16 posts views Thread by Paul Prescod | last post: by
4 posts views Thread by agent349 | last post: by
5 posts views Thread by James Foreman | last post: by
25 posts views Thread by Rainmaker | last post: by
95 posts views Thread by hstagni | last post: by
3 posts views Thread by Piotr Sobolewski | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.