How to coalesce many rows into one?

Question

I am using SSMS 2008 R2 and am simply trying to coalesce many rows into one. This should be simple I think, but it is currently repeating data in each row. Consider:

create table test
(
Name varchar(30)
)
insert test values('A'),('B'),('C')
select * from test

select distinct Name, coalesce(Name + ', ', '')
from test 

How can I rewrite this to achieve one row like: A,B,C

Answer 1

In SQL Server Transact-SQL, the easiest way to accomplish this is the following.

A table like this:

create table #foo
(
  id   int         not null identity(1,1) primary key clustered ,
  name varchar(32) not null ,
)

insert #foo (name) values ( 'a' )
insert #foo (name) values ( 'b' )
insert #foo (name) values ( 'c' )
insert #foo (name) values ( 'd' )
go

Can flattened using this seemingly dubious (but documented) technique:

declare @text varchar(max) = ''

select @text = @text
             + case len(@text)
                 when 0 then ''
                 else        ','
               end
             + t.name
from #foo t

select list_of_names = @text
go

yielding

list_of_names
-------------
a,b,c,d

Easy!