Aggregating strings

Serge Rielau

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

Bob Stearns

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

Serge Rielau

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

Brian Tkatch

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