Tuesday, August 19, 2008

Joining rows in T-SQL into a single delimited string

I think the easiest way to think of what I want is to think of how you can join elements in an array and create a delimited string. I want to do the same thing except instead of an array, I have rows in a database. I will describe what I am trying to do from a real world example in a simplified fashion.
Here is the scenario. I have a query that returns one column of data. That column of data is an email address. The end product that I want is a single string (varchar) with the emails from each row to be delimited by commas. Once I have that, then I can pass the string to msdb.dbo.sp_send_dbmail.

Here is a simple way to show you what I have without all the tables, complex query, etc. In real life I would have a query that is on a table, etc. :)
create view vEmails as
select
'joe.tester1@mycompany.com' as EmailAddress union
select
'mary.someone@companyxyz.com' union
select
'happy.luck@aces.com'
Now we can do
select EmailAddress from vEmails

Here is the output that I currently have.
joe.tester1@mycompany.com
mary.someone@companyxyz.com
happy.luck@aces.com
What I want is
joe.tester1@mycompany.com, mary.someone@companyxyz.com, happy.luck@aces.com

The criteria is I want an elegant solution that doesn't use cursors.  Here is my solution.

Option 1:

Declare @EmailList as varchar(MAX)
select
    @EmailList = ISNULL(@EmailList + ', ', '')
    + EmailAddress
from vEmails
ORDER BY EmailAddress
select @EmailList

NOTE: The same can be done using COALESCE instead of ISNULL. The parameters are identical in this example.
The result is
happy.luck@aces.com, joe.tester1@mycompany.com, mary.someone@companyxyz.com
I love this solution because it is clever and does not use cursors. It also works with rows that have a NULL email address. If you decide you want to generalize the solution into an aggregate function you can NOT do this in T-SQL. You will need to do this in VS2005 or greater using the Database Project and write it in C# or VB.NET. This is an entirely different solution. The solution here is unfortunately not really generic.

One last important thing I figured out is that if you add an ORDER BY 1 (ORDER BY EmailAddress works fine) the @EmailList variable will only contain the value from the last row, not the accumulation of rows separated by commas. I am speculating, but I think the issue is that the select statement does not actually select any data, it actually assigned the value to a variable. So, using an alias (or whatever it is technically called) instead of the actual column name in an order by, this causes some weird behavior. The moral here is use the full name in the order by and you should be fine.

I got most of my solution from:
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

Option 2:

An even nicer way to do this because it can be included as a sub-query is to use the following:

select STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(EmailAddress)) AS 'data()' FROM vEmails
FOR XML PATH('')),' #!',', '), 1, 2, '') as EmailAddresses

This was suggested by this page.

















10 comments:

Anonymous said...

very clever and elegant... thanx

Brent V said...

Dragon,

Thank you for the feedback. It is quite efficient as far as I can tell also. Enjoy.

NonGT said...

very nice!

I just need it at a few moment ago and I can't believe that I found it easily.

Thanks for a nice job.

Brent V said...

NonGT,

Awesome! Thank you for your feedback

Bharath said...

Awesome.. Simple and works great.

Thanx

Brent V said...

Bharath,

Thank you for the feedback. I love the kudos! :)

Thanks,

Brent

Gopal said...

Awesome Post man

Brent V said...

Gopal,

Thank you for the kind words. It is greatly appreciated.

Brent

Unknown said...

Hi,

Thank you for posting this nice bit of TSQL Code ! :)

Just wanted to share that I didn't create a Sql View and just used my table: RSVPEmailList
field: emailaddr

Declare @EmailList as varchar(MAX)
select
@EmailList = ISNULL(@EmailList + ', ', '')
+ emailaddr
from RSVPEmailList
ORDER BY emailaddr
select @EmailList

Thanks for LA, Ca.
Paul
paulchu.wordpress.com

Anonymous said...

Great!