|
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 | |
Share:
|
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 | | |
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 | | |
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. | | 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
| | | | | | | | | | |