How to consolidate different values into numbered column groups

Question

If I have a simple dataset, such as follows:

Name	Subject	Grade
Joe Bloggs	English	A
Joe Bloggs	Maths	B
Sam Smith	English	B
Sam Smith	Physics	C

Is there a way to create column groupings so that the data displays as follows:

Name	Subject 1	Grade 1	Subject 2	Grade 2
Joe Bloggs	English	A	Maths	B
Sam Smith	English	B	Physics	C

Any suggestions would be greatly appreciated!

I've tried various ways with nested tables, etc but the fact that you can't use aggregate functions in filters scuppers that approach.

Answer 1

As Thom A suggested, you'll need to add a column to handle your row groups but it's simple enough.

Here's I've created some sample data and added an extra row so you can see it working when the number of subjects varies between Names.

DECLARE @t TABLE (Name varchar(20), Subject varchar(20), Grade varchar(20))
INSERT INTO @T VALUES 
('Joe Bloggs', 'English', 'A'),
('Joe Bloggs', 'Maths', 'B'),
('Sam Smith', 'English', 'B'),
('Sam Smith', 'Physics', 'C'),
('Sam Smith', 'T-SQL', 'C')


SELECT 
    *
    , ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY Subject) AS ColN
    FROM @t

This gives us the following results

In your report, add a Matrix and drop Name into the Row placeholder and ColN into the column placeholder.

Right click the column header ([ColN]) and do "insert column Inside Group Right". Now just drop you subject and grade fields into the two columns.

You can refine the column header but I just left it as-is here. Here's the result.