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

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?

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

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.

There are different ways for Concatenating Row Values in Transact-SQL.

Adding comma delimiter...

DECLARE @Concat VARCHAR(MAX)

SET @Concat = ''

SELECT  @Concat = @Concat + LEFT(',', LEN(@Concat)) + ISNULL(Field, '') FROM    dbo.Table

SELECT  @Concat