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?

possible duplicate of [Is there a way to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?](http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-sql-server-function-to-join-multiple-rows-from-a-sub) — Martin Smith, May 13 '11 at 18:31
The basic idea of this question has been asked several times. Take a look at the links provided in the answers below. — TKTS, May 13 '11 at 18:36
@TKTS - I wouldn't be surprised if this is the most rampant duplicate on SO. It seems to be asked at least once a day, sometimes more often. — Martin Smith, May 13 '11 at 18:38

score 9 · Answer 1 · answered May 13 '11 at 18:57

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)

score 3 · Answer 2 · answered May 13 '11 at 18:34

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.

score 1 · Answer 3 · edited May 23 '17 at 12:22

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

score 1 · Answer 4 · answered May 13 '11 at 19:31

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

score 1 · Answer 5 · edited Oct 01 '12 at 14:04

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

score 0 · Answer 6 · edited Aug 05 '14 at 13:58

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.

Output a comma separated list in T-SQL

6 Answers6

Linked