Concatenating multiple rows fields into one column in T-SQL

Question

I am writing an SQL query in which that I will need to perform a sub select on a table, which will usually return multiple rows. I need to be able to join together the results of a certain field from all the rows into one field to output. Is this possible, and how?

For example, if the SQL query returns

id | field
1  | test1
2  | test2
3  | test3

I need the outputted field to be "test1 test2 test3". Thanks

Answer 1

Here's the for xml trick to do that:

    SELECT  field + ' ' as [text()]
    FROM    YourTable
    FOR XML PATH ('')

This prints:

test1 test2 test3

It's typically used with an outer apply to execute it once for each row.

Answer 2

declare @sample table(id int, field varchar(20))
insert into @sample values(1,'test1')
insert into @sample values(2,'test2')
insert into @sample values(3,'test3')
declare @result varchar(max) set @result = ''
select @result = @result + ' '+field from @sample
select @result

A SQLCLR custom aggregator would be a an alternative (read better) solution

Answer 3

Try this:

SELECT RTRIM(field)
  FROM (
                SELECT field + ' ' field
                    FROM <YOUR_TABLE>
                    FOR XML PATH('')
             ) a

Answer 4

As an addition to the existing answers. Try including the COALESCE expression with column name your going to use. This avoids having null values in your concatenated string and avoid your list looking like this. Notice the redundant blank space.

field1 field2   field4 field

Further details can be found here.

GO

DECLARE @tableName VARCHAR(MAX)
SELECT  @tableName = COALESCE(@tableName + ' ' ,'') + Name
FROM    sys.tables
SELECT  @tableName

GO

Answer 5

it is possible to do with a cursor.

declare @field nvarchar(max)
declare @concat nvarchar(max)
set @concat = ''
declare @cursor cursor
set @cursor = cursor for select field from table
open @cursor
fetch next from @cursor into @field
while @@fetch_status = 0
begin
  set @concat = concat(@concat,@field)
  fetch next from @cursor into @field
end

your exercise is to add space between the concatenated strings :-)