[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1331
  • Last Modified:

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
0
jdroger2
Asked:
jdroger2
  • 3
  • 3
  • 3
1 Solution
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now