How to convert a recordset to a delimited string i

Question

Is there a way to convert a single column record set such as

1
2
3

into '1,2,3' string in SQL Server?

Answer 1

COALESCE is decent way to do this

http://msdn.microsoft.com/en-us/library/ms190349.aspx

DECLARE @Out VARCHAR(2048)
Select @Out = COALESCE(@Out + ', ', '') + [YourColumn] from YourTable

print @Out

Answer 2

select stuff( (select ',' + YourColumn
               from YourTable
               for xml path('')), 1, 1, '')

Note: The stuff function just removes the first comma from the result string.

Answer 3

I've had success creating a function using Coalesce like below to do this. You can then use the function in your select statement to get the delimited string. (You can add parameters to your function and use them in the coalesce select statement as needed.)

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