I have data from a SQL query that looks like the table on the left but I want to use SSRS to make it look like the table on the right. Is this possible?
I'm willing to modify the SQL if neccesary, assume it is currently of the form
SELECT
Name
,Capital Visited
From
Trips
You can use CROSS APPLY for this combined with XML PATH functions as follows.
It looks a bit lengthy but it includes sample data matching your sample so you can test it before you apply it to your real tables.
DECLARE @trips TABLE([Name] varchar(50), [Capital Visited] varchar(50))
INSERT INTO @trips
VALUES
('Joe', 'London'),
('Fred', 'Tokyo'),
('Joe', 'Berlin'),
('Bob', 'Paris'),
('Fred', 'London'),
('Fred', 'Madrid'),
('Bob', 'Rome')
/* Uncomment below to check the table data looks as expected */
-- SELECT [Name] ,[Capital Visited] From @trips
SELECT DISTINCT
[Name], cx.Captials
FROM
@trips t
CROSS APPLY ( SELECT Stuff(
(
SELECT ', ' + [Capital Visited]
FROM @trips WHERE [Name] = t.[Name]
FOR XML PATH('')
), 1, 2, '') AS Captials
) cx
This gives you following results
Name Captials
Bob Paris, Rome
Fred Tokyo, London, Madrid
Joe London, Berlin
Rather than me explaining the answer in full, there is a reasonable explaination here.
As an SSRS solution, you can combine Join and LookupSet to concatenate all the values in a group. Within a table grouped by NAME, use this expression:
=Join(LookupSet(Fields!NAME.Value, Fields!NAME.Value, Fields!CAPITAL_VISITED.Value, "DataSet1"), ", ")
LookupSet() gets you all the values from a dataset based on a primary key and returns them as an array. (It's typically used to get values from a different dataset, but in this case we use just one). Join() then concatenates all the values found the array using a chosen delimiter (", ").
