Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

String Aggregation in T-SQL & PL-SQL : SQL Nugget

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, '')  children
FROM t b 
GROUP BY parent

which, yes, turned this:

image

into this:

image

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 t
group by parent

So, now you know. And so will I if I ever need to find this again!

@Jamiet

Published Thursday, July 16, 2009 1:32 PM by jamie.thomson

Comments

 

David.Portas said:

The gotcha with using the the XML functions is that special characters such as "<" and "&" get XML-style quoting: "&amp;" and "&lt;". OK if the result is treated as XML but not so good if what you actually wanted was a string.

July 16, 2009 8:50 PM
 

khodges said:

My knowledge of ORACLE is zero so this may not be of any use to you but I will mention it just in case.  There is another concatenation method that could help:  the COALESCE method.

DECLARE @DepartmentName VARCHAR(1000)

SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';'  

FROM AdventureWorks.HumanResources.Department

WHERE GroupName = 'Executive General and Administration'

SELECT @DepartmentName AS DepartmentNames

July 17, 2009 1:20 AM
 

khodges said:

I should add - in testing the COALESCE method I have found it to be slower than the FOR XML method... but it does have its uses from time to time even so.

July 17, 2009 1:23 AM
 

Dew Drop – July 17, 2009 | Alvin Ashcraft's Morning Dew said:

July 17, 2009 1:18 PM
 

Sanjeev Agarwal said:

Daily tech links for .net and related technologies - July 18-21, 2009 Web Development A (less) simple

July 20, 2009 10:11 AM
 

jamie.thomson said:

Hi kristen,

Yeah, that is indeed another method but it does have one distinct disadvantage. it is procedural i.e. its not a single SELECT statement.

thanks though - useful to know about that method too.

-Jamie

August 4, 2009 5:25 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems