CODEWITHSUNDEEP
sqlsql-serversql-server-group-concat
f00860
f00860

Reputation: 3576

Group and concatenate many rows to one

I want to "concatenate" all the "Text"-rows into one single row and get one row as a result. Is this even possible? I use MSSQL Server 2005.

query

Upvotes: 1

Views: 4016

Answers (3)

VladV
VladV

Reputation: 10349

Use FOR XML PATH:

SELECT [Text]+' ' AS 'text()' FROM _table FOR XML PATH('')

Another option - use string concatenation:

DECLARE @s nvarchar(max)
SELECT @s = ISNULL(@s, '') + t + ' '  FROM _table OPTION (MAXDOP 1)
SELECT @s

Please note that the latter one isn't guaranteed to work, afaik, officially the behaviour of "@s = @s + ..." for multi-row resultset is undefined.
MAXDOP 1 hint is used here to prevent the optimizer from creating a parralel execution plan, as this will yield an incorrect result for sure.

Upvotes: 2

Fermin
Fermin

Reputation: 36081

Something like:

DECLARE @result varchar(max)

SELECT @result = COALESCE(@result + ' ','') +[Text] FROM [Table]
SELECT @result

Upvotes: 0

John Bledsoe
John Bledsoe

Reputation: 17642

I believe you're looking for something like this:

DECLARE @string nvarchar(max)
SET @string = N''

SELECT @string = @string + [Text] + N' ' FROM [YourTable]

SELECT @string

This will concatenate all of the values for the [Text] column into a single variable. You can then select the variable to retrieve all of the values in a single row.

Upvotes: 2

Related Questions