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:
very clever and elegant... thanx
Dragon,
Thank you for the feedback. It is quite efficient as far as I can tell also. Enjoy.
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.
NonGT,
Awesome! Thank you for your feedback
Awesome.. Simple and works great.
Thanx
Bharath,
Thank you for the feedback. I love the kudos! :)
Thanks,
Brent
Awesome Post man
Gopal,
Thank you for the kind words. It is greatly appreciated.
Brent
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
Great!
Post a Comment