How to concatenate all the records in a column ret

问题:

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?

回答1:

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

回答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.

回答3:

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

回答4:

Adding comma delimiter...

DECLARE @Concat VARCHAR(MAX)

SET @Concat = ''

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

SELECT  @Concat