Posted by: Serge Rielau
One of the FAQs I see in newsgroups is the request on how to aggregate strings.
That is, how do I collapse rows of a string columns into e.g. a comma separated list.
DB2 (as of 9.7.3) does not have any built-in function for this, but there are interesting ways to get the job done.
Let's use an employee table as an example:
CREATE TABLE emp(
empid INT NOT NULL PRIMARY KEY,
name VARCHAR(20),
salary DECIMAL(10, 2),
dept VARCHAR(20));
INSERT INTO emp VALUES
(1, 'Jones' , 20000, 'Research'),
(2, 'Schmidt' , 23000, 'Research'),
(3, 'Carpenter', 19000, 'Sales'),
(4, 'Newman' , 28000, 'Development'),
(5, 'Kohler' , 27000, 'Research'),
(6, 'Wang' , 26000, 'Development'),
(7, 'Wu' , 25000, 'Sales'),
(8, 'McKnight' , 19500, 'Marketing');
Let's find out about the average salary by department:
db2 => select dept, avg(salary) as avgsal from emp group by dept;
DEPT AVGSAL
-------------------- ----------------------
Development 27000
Marketing 19500
Research 23333
Sales 22000
4 record(s) selected.
So far so good. Now we want in addition also get a list of all the employees within the department - comma separated.
SELECT
dept,
SUBSTR(XMLCAST(XMLGROUP(',' || name AS a
ORDER BY name)
AS VARCHAR(60)), 2) AS Names,
avg(salary) as avgsal
FROM emp
GROUP BY dept;
DEPT NAMES AVGSAL
-------------------- ------------------------------------ -----------------------
Development Newman,Wang 27000
Marketing McKnight 19500
Research Jones,Kohler,Schmidt 23333
Sales Carpenter,Wu 22000
4 record(s) selected.
Done!
OK, I take it you want to know just what happened here, so let's pick this query apart:
XMLGROUP turns it's input which consists of n parameters and m rows into an XML document:
select XMLGROUP(',' || name AS a order by name) as xmldocs from emp group by dept;
XMLDOCS
----------------------------------------------------------
<rowset><row><A>,Newman</A></row><row><A>,Wang</A></row></rowset>
<rowset><row><A>,McKnight</A></row></rowset>
<rowset><row><A>,Jones</A></row><row><A>,Kohler</A></row><row><A>,Schmidt</A></row></rowset>
<rowset><row><A>,Carpenter</A></row><row><A>,Wu</A></row></rowset>
4 record(s) selected.
So what we have done here is group the rows by department in a manner that we didn't lose any of the name-information/
Now all we need to do is strip out all the XML. We could do this the hard way or we employee some more XML functions
In this case XMLCAST is the right choice. It will turn the XML into a string and also remove all the tags.
The only remaining issue we have afterward is that there is a leading comma.
The SUBSTR(text, 2) expression will cut of the first byte.
#Db2