I think it's common to come across a requirement where you have to retrieve values from a column in a database table. For this example however, we would use a table variable instead.
DECLARE @EmailTable TABLE
(
Email VARCHAR(64)
)
— then we insert sample values
INSERT INTO @EmailTable VALUES ('test@ryangaraygay.com')
INSERT INTO @EmailTable VALUES ('test@yahoo.com')
INSERT INTO @EmailTable VALUES ('test@gmail.com')
The common solution of course is to append/concatenate the values returned from the query.
** Note that you have to set @Emails to '' before the actual concatenation [more]
DECLARE @Emails VARCHAR(MAX)
SET @Emails = ''
SELECT @Emails = @Emails + Email + ','
FROM @EmailTable
PRINT @Emails
would result to : test@ryangaraygay.com,test@yahoo.com,test@gmail.com,
But notice that you will have an extra ',' (or whatever delimiter you are using) at the end of the resulting value. To remove this, one way is to use substring (SUBSTR) to return the value minus the delimiter at the end. But it would in turn also mean that you have to check that @Emails is actually not empty (and maybe if it ends with the delimiter or not) making such a relatively simple tasks involve more code. (I won't even include here since you don't really want to know about it)
This however, I believe is a much simpler way to do it with less code and complexity.
DECLARE @Emails VARCHAR(MAX)
SELECT @Emails =
ISNULL(@Emails + ',', '') + Email
FROM @EmailTable
PRINT @Emails
would result to : test@ryangaraygay.com,test@yahoo.com,test@gmail.com
Almost the same result, except that there would be no trailing "," (or whatever delimiter) at the end nor at the start anymore. Try It!
The trick here is that ISNULL(@Emails + ',', '') returns '' for the first time the statement is executed.
Which also brings up an interesting point that if you add a string/text to a NULL, it would result to a NULL (not sure if ANSI NULL ON/OFF has effect on this). Since @Emails was not initialized to any value before it was used, the statement will return '' for the first time it is encountered in the SELECT query. The first statement would simpy be the email then all the succeeding values would be appended as : , <Email>
I'm quite sure I haven't known this by myself but rather from some source/code I've seen before which unfortunately I could not recall to acknowledge properly. I know it's a common approach but I still see the old way (substr) of doing it even now so I might as well post it. And to be honest, the first time I encountered the requirement, I didn't really view it as simple as it was with this now.