CODEWITHSUNDEEP
sqlt-sql
Ivan
Ivan

Reputation: 64227

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?

Upvotes: 4

Views: 8351

Answers (4)

John Fowler
John Fowler

Reputation: 1

Adding comma delimiter...

DECLARE @Concat VARCHAR(MAX)

SET @Concat = ''

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

SELECT @Concat

Upvotes: 0

MartW
MartW

Reputation: 12538

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.

Upvotes: 4

Michael Buen
Michael Buen

Reputation: 39483

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

Upvotes: 6

Giorgi
Giorgi

Reputation: 30893

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

Upvotes: 2

Related Questions