Output a comma separated list in T-SQL

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 开发者_JAVA百科separated list of phone numbers. What's the easiest way to do this in sql? A while loop?

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)

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.

FWIW I created a SQL CLR Aggregate function. Works like a champ!

[Serializable] [SqlUserDefinedAggregate(Format.UserDefined, Name = "JoinStrings", IsInvariantToNulls=true, IsInvariantToDuplicates=false, IsInvariantToOrder=false, MaxByteSize=8000)] public struct JoinStrings : IBinarySerialize { public string Result;

public void Init()
{
    Result = "";
}
public void Accumulate(SqlString value)
{
    if (value.IsNull)
        return;

    Result += value.Value + ",";

}
public void Merge(JoinStrings Group)
{
    Result += Group.Result;
}

public SqlString Terminate()
{
    return new SqlString(Result.ToString().Trim(new

char[] { ',' })); }

public void Read(System.IO.BinaryReader r)
{
    Result = r.ReadString();
}

public void Write(System.IO.BinaryWriter w)
{
    w.Write(Result.ToString());
} }

I can then use it like this:

SELECT dbo.JoinStrings(Phone) FROM Phones Where UserID = XXX

See my answer from this question. There are a couple of other ways to do it listed in that question also. COALESCE or for xml path should do the trick though.

Edit (added my answer from the previous question):

CREATE FUNCTION [dbo].[fn_MyFunction]()RETURNS NVARCHAR(MAX)
 AS 
BEGIN    
    DECLARE @str NVARCHAR(MAX)    
    DECLARE @Delimiter CHAR(2)     
    SET @Delimiter = ', '    
    SELECT @str = COALESCE(@str + @Delimiter,'') + AColumn    
     FROM dbo.myTable    
    RETURN RTRIM(LTRIM(@str))
 END

Assuming you have a Customers table which has a unique ID and another table named PhoneNumbers with multiple phone numbers for each customer sharing the Customer ID field as a Foreign Key this would work using a correlated sub-Query

Select C.ID, C.FirstName, C.LastName,
(select (STUFF(( SELECT ', ' + PhoneNumber from PhoneNumbers P where P.CID = C.ID
FOR XML PATH('')), 1, 2, ''))) as PhoneNumbers
from Customers C

Select Unique ID, Replace(Rtrim(Ltrim(Case when [Phone_Number1] is not null Then [Phone_Number1]+'  ' Else '' End +
                  Case when [Phone_Number2] is not null Then [Phone_Number2]+'  ' Else '' End +
                  Case when [Phone_Number3] is not null Then [Phone_Number3]+'  ' Else '' End)),'  ',', ') as Phone_numbers

From MYTable

Hope this is what you are looking for and I dont know if this will help you so far after the question.