How to concatenate all the records in a column returned by a query into one varchar string in T-SQL?

Question

A query (SELECT/FUNCTION/VIEW/PROCEDURE) returns a column of varchar records. I need to concatenate them all into one single varchar line. How do I best do it in T-SQL?

Answer 1

declare @s varchar(8000)
select @s = coalesce(@s + ', ' + col, col) from tbl

Answer 2

DECLARE @Concat varchar(MAX)

SELECT @Concat = ''

SELECT @Concat = @ConCat + IsNull(Field1, '')
FROM Table1

SELECT @Concat

This will return a single value which is the concatenation of every Field1 value. The IsNull part will mean NULL values do not mess things up. Of course, if you're not using SQL Server 2005 or later then you can't use varchar(MAX) and the number of records you're concatenating will become an issue quicker.