From a one to many SQL dataset Can I return a comma delimited list in SSRS?

Question

I am returning a SQL dataset in SSRS (Microsoft SQL Server Reporting Services) with a one to many relationship like this:

ID REV Event

6117 B FTG-06a
6117 B FTG-06a PMT
6117 B GTI-04b
6124 A GBI-40
6124 A GTI-04b
6124 A GTD-04c
6136 M GBI-40
6141 C GBI-40

I would like to display it as a comma delimited field in the last column [Event] like so:

ID REV Event
6117 B FTG-06a,FTG-06a PMT,GTI-04b
6124 A GBI-40, GTI-04b, GTD-04c
6136 M GBI-40
6141 C GBI-40

Is there a way to do this on the SSRS side of things?

Answer 1

You want to concat on the SQL side not on the SSRS side, that way you can combine these results in a stored procedure say, and then send it to the reporting layer.

Remember databases are there to work with the data. The report should just be used for the presentation layer, so there is no need to tire yourself with trying to get a function to parse this data out.

Best thing to do is do this at the sproc level and push the data from the sproc to the report.

Based on your edit this is how you would do it:

To concat fields take a look at COALESCE. You will then get a string concat of all the values you have listed. Here's an example:

use Northwind

declare @CategoryList varchar(1000)
select @CategoryList = coalesce(@CategoryList + ‘, ‘, ”) + CategoryName from Categories

select ‘Results = ‘ + @CategoryList

Now because you have an additional field namely the ID value, you cannot just add on values to the query, you will need to use a CURSOR with this otherwise you will get a notorious error about including additional fields to a calculated query.

Take a look here for more help, make sure you look at the comment at the bottom specifically posted by an 'Alberto' he has a similiar issue as you do and you should be able to figure it out using his comment.