Sometimes there is a need to concatenate strings from a column into a single row. Some databases already provide a solution with the GROUP_CONCAT() aggregate function, such as SQLite and MySQL. In DB2 you can use the XMLAGG() function to aggregate the strings from different rows into a single XML string, and then transform this string into a comma – or any other character – separated list of string values. An almost identical approach can be used on Oracle and SqlServer databases; the SQL syntax will vary slightly from the one shown below.
Consider the following tables and their contents:
The following query will get all the persons and the fruit they like:
select p.name as person, nvl(f.name, 'Nothing') as fruit
from person p
left outer join person_fruit pf on p.person_pk = pf.person_fk
left outer join fruit f on pf.fruit_fk = f.fruit_pk
order by p.name;
In order to display one row per person, and to group (concat) the fruit each person likes, you can use the following query:
select p.name as person,
nvl(trim(',' from replace(replace(replace(
cast(xml2clob(XMLAGG(xmlelement(name "E",f.name))) as varchar(2000)),
'<E>',''),'</E>',','), '<E/>', 'Nothing')), 'Nothing') as fruit
from person p
left outer join person_fruit pf on p.person_pk = pf.person_fk
left outer join fruit f on pf.fruit_fk = f.fruit_pk
group by p.name
order by p.name;
Which will give the following result:
The real magic is done by the XMLAGG() function, which groups the fruit values together into a single XML structure. The order of these values in the XML structure is undetermined. For instance for Ellen the XML value would be “<E>Pears</E><E>Bananas</E><E>Grapes</E>”, which is then converted into string “Pears</E>Bananas</E>Grapes</E>”, then to “Pears,Bananas,Grapes,” and once again to “Pears,Bananas,Grapes”. In case there are no fruit values, such as is the case with William, the XML string would be “<E/>”, which is converted to string value “Nothing”. In case NULL is returned, we return “Nothing” instead.