In my current day job I’m doing a lot of work against an Oracle back-end and I’ve just come across a situation where I need to do some string aggregation. Effectively I needed to turn this:
Parent |
Child |
| Charles |
William |
| Charles |
Harry |
| Anne |
Peter |
| Anne |
Zara |
| Andrew |
Beatrice |
| Andrew |
Eugenie |
into this:
Parent |
Children |
| Charles |
William,Harry |
| Anne |
Peter,Zara |
| Andrew |
Eugenie,Beatrice |
In other words I wanted to take a list of children per parent and produce a comma-delimited list of each of their children.
I know how to do this in T-SQL, you use the FOR XML PATH(‘’) construct like so:
with t as(
select 'Charles' parent, 'William' child union
select 'Charles', 'Harry' union
select 'Anne', 'Peter' union
select 'Anne', 'Zara' union
select 'Andrew', 'Beatrice' union
select 'Andrew', 'Eugenie'
)
SELECT parent, STUFF( ( SELECT ','+ child
FROM t a
WHERE b.parent = a.parent
FOR XML PATH('')),1 ,1, '') childrenFROM t b
GROUP BY parent
which, yes, turned this:
into this:
Unfortunately I didn’t know how to accomplish it in Oracle however after a bit of searching around I found the answer:
with t as(
select 'Charles' parent, 'William' child from dual union
select 'Charles', 'Harry' from dual union
select 'Anne', 'Peter' from dual union
select 'Anne', 'Zara' from dual union
select 'Andrew', 'Beatrice' from dual union
select 'Andrew', 'Eugenie' from dual
)
select parent, rtrim(xmlagg(xmlelement(e,child || ','))
.extract('//text()'),',') childs from tgroup by parent
So, now you know. And so will I if I ever need to find this again!
@Jamiet