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 'firstname.lastname@example.org' as EmailAddress union
select 'email@example.com' union
Now we can do
select EmailAddress from vEmails
Here is the output that I currently have.
What I want is
firstname.lastname@example.org, email@example.com, firstname.lastname@example.org
The criteria is I want an elegant solution that doesn't use cursors. Here is my solution.
Option 1:Declare @EmailList as varchar(MAX)
@EmailList = ISNULL(@EmailList + ', ', '')
ORDER BY EmailAddress
NOTE: The same can be done using COALESCE instead of ISNULL. The parameters are identical in this example.
The result is
email@example.com, firstname.lastname@example.org, email@example.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:
Option 2:An even nicer way to do this because it can be included as a sub-query is to use the following:
This was suggested by this page.