One row, seperated by commas for a list

EricLynnWright
EricLynnWright used Ask the Experts™
on
I'm trying to get sql that will return a list of items but in ONE row and each item separated by commas.


Current:

Select col1 from table1
     
Results:
   item1
   item2
   item3


Future:

Results:
   item1, item2, item3
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
declare @onerow varchar(max) 
 
;with cte as  
( 
    select distinct item1 + ' ' + item2 + ' ' + item3 as oneName  
    from yourtable yt  

) 
select @onerow = isnull(@onerow + char(13), '') + oneName 
from cte b 
 
print @oneName 

Open in new window

Top Expert 2011

Commented:
small mistake

declare @onerow varchar(max) 
 
;with cte as  
( 
    select distinct item1 + ', ' + item2 + ' ,' + item3 as oneName  
    from yourtable yt  

) 
select @onerow = isnull(@onerow + char(13), '') + oneName 
from cte b 
 
print @oneName 

Open in new window

just do

select stuff((select ',' + col1 from table1 for xml path('')), 1, 1, '')
You can do it with a variable:

declare @list varchart(8000)

select 
	@list=isnull(@list,'')+col+','
from 
	yourtable
-- use this trick to remove last comma and return the list	
Select replace(@list+'#$&',',#$&','') as List

Open in new window



or using FOR XML:

SELECT CONVERT(VARCHAR(1000),col+',')
            FROM yourtable
            FOR XML PATH('') 

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial