Comments (6) Visits (10417)
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:
Let's find out about the average salary by department:CREATE TABLE emp(
So far so good. Now we want in addition also get a list of all the employees within the department - comma separated.db2 => select dept, avg(salary) as avgsal from emp group by dept;
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:
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/select XMLGROUP(',' || name AS a order by name) as xmldocs from emp group by dept;
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.