Easy table pivot

-- create a temp table to hold some data
If (Select object_id('tempdb.dbo.#test')) > 0
   Exec ('Drop table #test')

create table #test(name varchar(30) null)
Go

-- insert some data for the example
set nocount on
insert into #test values('myname')
insert into #test values('yourname')
insert into #test values('hisname')
insert into #test values('hername')
insert into #test values('therename')
Go

-- pivot the results
declare @value varchar(255)
set @value = ''
select @value = @value + name + '  ' from #test
select @value

-- add some formatting and order the results
set @value = ''
select @value = @value + name + ', ' from #test order by name
select substring(@value, 1, len(@value)-1)
Go

-- show the original table
select * from #test
Go


-- Results
/*
----------------------------------------------
myname  yourname  hisname  hername  therename  

                                              
----------------------------------------------
hername, hisname, myname, therename, yourname

name                           
------------------------------ 
myname
yourname
hisname
hername
therename
*/