comma delimited list in SSRS or SQL

I am using SQL Server 2008 Reporting Services and in my report I need to display all of the occurrences of a field in my dataset as a comma delimited list.  IE, if I have a table of colors, I want to list them in my report like: Red, Blue, White, Green

If there is a way to do this in T-SQL instead of SSRS I am open to that as well.

Thanks
LVL 1
jdroger2Asked:
Who is Participating?
 
lofCommented:
Sure there is a way. Here is a sample for you
create table Colours (id int identity, colour varchar(32))
go

insert into Colours values ('Red')
insert into Colours values ('Green')
insert into Colours values ('Blue')

declare @list as varchar(max)
select @list = coalesce(@list+', ' + colour, colour) from Colours
select @list

Open in new window

0
 
jdroger2Author Commented:
is there a way to do this all in one query or would I have to create a stored procedure to get this into SSRS?
0
 
lofCommented:
you can not have a select statement to assign and return values so if you need to have one call from SSRS you will have to use stored procedure or a function returning a table
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
ralmadaCommented:
Try like this:
select replace((select color as [data()] from colortable for xml path('')), ' ', ',')

Open in new window

0
 
lofCommented:
Ralmada, how about colours like 'Deep blue', your method will convert it to 2 colours 'Deep' and 'blue'?
0
 
jdroger2Author Commented:
yes i do have multi-word data, like "deep blue:
0
 
ralmadaCommented:
Not a problem
select stuff((select  ',' + color from colortable for xml path('')), 1, 1, '')

Open in new window

0
 
jdroger2Author Commented:
ralmada's solution may work but i already implemented lof's.  thanks all
0
 
ralmadaCommented:
>>ralmada's solution may work<<
I'm positive that it works. In these cases you should really consider splitting points in order to appreciate both experts time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.