Reporting Services - show multiple values for a column horizontally rather than vertically

Question

I have a report where row data can have the same data, apart from the data in the last column. Just adding the data to a table results in this:

Column 1	Column 2	Column 3	Column 4
1	abc	1111	234345
1	def	2222	435656
1	def	2222	423233
1	xyz	1234	145423

I want to show the data like this, where if a row has multiple values for Column 4 value, additional Column 4's are added horizontally:

Column 1	Column 2	Column 3	Column 4	Column 4
1	abc	1111	234345
1	def	2222	435656	423233
1	xyz	1234	145423

I've tried adding a Parent Group to Column 4, which is close to what I want, but every row is given it's own column for the Column 4 value so it ends up like this:

Column 1	Column 2	Column 3	Column 4	Column 4	Column 4	Column 4
1	abc	1111	234345
1	def	2222		435656	423233
1	xyz	1234				145423

etc...

Is there a way to achieve the layout I require?

Answer 1

You can do this with a small change to your dataset query.

Here I have recreated your sample data table as a table variable called `@t' . Then I query the table and add a column which gives us a unique index for each 'Column4' value within each Column1-3 group

DECLARE @t TABLE (Column1 int,  Column2 varchar(10), Column3 int, Column4 int)
INSERT INTO @t VALUES
(1, 'abc', 1111, 234345) ,
(1, 'def', 2222, 435656) ,
(1, 'def', 2222, 423233) ,
(1, 'xyz', 1234, 145423) 

SELECT 
          *
        , ROW_NUMBER() OVER(PARTITION BY Column1, Column2, Column3 ORDER BY Column4) as Col4Index
    FROM @t

Now in your report, add a matrix with one rowgroup. This will group on Column1, Column2 and Column3

Now add a column group that is grouped on Col4Index

Add your first 3 columns to the matrix making sure they are all in the single rowgroup (just add additional columns inside the group first and then select the correct field for each.

Drop the Column4 field into the [Data] placeholder and finally set the header for this column to an expression (optional) like this

="Column4_" & Fields!Col4Index.Value

The report design looks like this

The final output looks like this