CODEWITHSUNDEEP
asp.netsqlt-sql
Chris
Chris

Reputation: 7621

Concatenating multiple rows fields into one column in T-SQL

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

Upvotes: 2

Views: 11807

Answers (5)

Christo
Christo

Reputation: 2370

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

Upvotes: 0

Andomar
Andomar

Reputation: 238266

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.

Upvotes: 5

Chandu
Chandu

Reputation: 82933

Try this:

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

Upvotes: 0

Axarydax
Axarydax

Reputation: 16623

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

Upvotes: -1

Ralph Shillington
Ralph Shillington

Reputation: 21108

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

Upvotes: 3

Related Questions