Output a comma separated list in T-SQL

Question

I have a table with phone numbers in it. Instead of spitting out a single row for each number I want to return a comma separated list of phone numbers. What's the easiest way to do this in sql? A while loop?

Answer 1

Some of those answers are overly complicated with coalesce and more complex XML queries. I use this all the time:

select @Phones=(
  Select PhoneColumn+','
  From TableName
  For XML Path(''))
-- Remove trailing comma if necessary
select @Phones=left(@Phones,len(@Phones)-1)

Answer 2

You could create a UDF that would do something like this

CREATE FUNCTION dbo.GetBirthdays(@UserId INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @combined VARCHAR(MAX)
SELECT  @combined = COALESCE(@combined + ', ' + colName + ', colName)
FROM    YourTable
WHERE UserId = @UserId
ORDER BY ColName

END

Basically this just pulls all of the values into a simple list.