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. :)
Now we can do
select EmailAddress from vEmails
Here is the output that I currently have.
What I want is
The criteria is I want an elegant solution that doesn't use cursors. Here is my solution.
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
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: