How to turn one column of a table into a csv string in SQL Server without using a cursor

Question

I want to return the results of select Column from Table into a comma separated string using SQL Server.

The column in question is rather large (nvarchar(2000)) so the solution has to be able to handle very large result values.

Answer 1

DECLARE @result nvarchar(max)
SET @result = ''

SELECT @result = @result + [Column] + N','
FROM [TABLE]

//TODO: trim last ',' if you require

PRINT @result

If Column can be null, then either exclude it first, or use ISNULL/COALESCE - otherwise a single NULL will break the entire sequence. It is more efficient to exclude it with a WHERE:

SELECT @result = @result + [Column] + N','
FROM [TABLE]
WHERE [Column] IS NOT NULL

Answer 2

without the trailing comma version:

declare @s varchar(max);

select @s = isnull(@s + ', ' + lastname, lastname)
from person
order by lastname;

print @s;