How to convert a recordset to a delimited string in SQL Server

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

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