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

Exact duplicate of this: http://stackoverflow.com/questions/941103/concat-groups-in-sql-server and many other questions. — Larry Lustig, Jan 19 '11 at 19:08
...and I assume the answerers have not read this http://blog.stackoverflow.com/2011/01/the-wikipedia-of-long-tail-programming-questions/ — gbn, Jan 19 '11 at 19:18
@gbn: Well, I've read that blog post. It does not list one successful long tail answer, and I don't think they exist. — Andomar, Jan 19 '11 at 19:22

score 5 · Answer 1 · answered Jan 19 '11 at 19:14

5

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.

answered Jan 19 '11 at 19:14

Andomar

232,371
49
380
404

score 3 · Accepted Answer · answered Jan 19 '11 at 19:11

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

Christo · Answer 3 · 2016-02-20T10:39:12.340

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

score 0 · Answer 4 · answered Jan 19 '11 at 19:14

0

Try this:

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

answered Jan 19 '11 at 19:14

Chandu

81,493
19
133
134

On my machine, this gives: `No column name was specified for column 1 of 'a'.` – Andomar Jan 19 '11 at 19:16
Updated the post to reflect the column name as specified. – Chandu Jan 19 '11 at 19:17
You'd have to change `) a` to `) a(field)`; then it would print `field1 field2 field3 `, which you can fix by changing `SELECT field + ' ' field` to `SELECT field + ' ' as [text()]` – Andomar Jan 19 '11 at 19:18

score -1 · Answer 5 · answered Jan 19 '11 at 19:11

-1

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 :-)

answered Jan 19 '11 at 19:11

Axarydax

16,353
21
92
151

http://stackoverflow.com/questions/58141/why-is-it-considered-bad-practice-to-use-cursors-in-sql-server – Tim Dec 08 '14 at 01:13

Concatenating multiple rows fields into one column in T-SQL

5 Answers5