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

Question

The query (SELECT / FUNCTION / VIEW / PROCEDURE) returns a column of varchar records. I need to combine them all in one varchar line. What is the best way to do this 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 returns a single value, which is the concatenation of each value of Field1. The IsNull part will mean that NULL values ​​will not ruin things. Of course, if you are not using SQL Server 2005 or later, you cannot use varchar (MAX), and the number of records you concatenate will become a faster problem.

Answer 3

There are various ways to combine row values ​​in Transact-SQL.

Answer 4

Adding a comma delimiter ...

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