How do I display a wide table in SSRS containing lists of names by group?

Question

I have a list of students assigned to classes. Each student is in one class only. The numbers of students in each class is not always the same.

Student1,Class1
Student2,Class1
Student3,Class1
Student4,Class2
Student5,Class2
Student6,Class3
...

I want to display them in SSRS where each class is a column, and the names of students appear under the class heading, like this:

  Class1  |  Class2  |  Class3  | ...
-------------------------------------------
Student1  | Student4 | Student6 | ...
Student2  | Student5 | Student7 |
Student3  |          | Student8 |

I tried using a matrix in SSRS, but have struggled because I keep getting a matrix like this:

  Class1  |  Class2  |  Class3  | ...
-------------------------------------------
Student1  |          |          |
          |Student4  |          |
          |          | Student6 | 
Student2  |          |          |

I thought about using a SQL PIVOT function but you have to specify the pivoting values, and I would like the table to generate dynamically based on what is found in the data.

Is there any way of achieving this?

Thank you all for your time.

Edit: I also should point out that I can't get a PIVOT to work correctly either, even if I test it without using Dynamic SQL. For example:

select * 
from (
    select Name, Class
    from (values 
        ('Student1', 'Class1'),
        ('Student2', 'Class1'),
        ('Student3', 'Class1'),
        ('Student4', 'Class2'),
        ('Student5', 'Class2'),
        ('Student6', 'Class3'),
        ('Student7', 'Class3'),
        ('Student8', 'Class3'),
        ('Student9', 'Class3')
        ) as T(Name, Class)
    ) as T
    PIVOT (
        max(Name)
        for Class in ([Class1],[Class2],[Class3])
    ) as P

Output:

Class1      Class2      Class3
Student3    Student5    Student9

A further problem with PIVOT is that I would ideally like to use other data elements to format cells in the SSRS report (e.g. student gender to set fill colour of cell and so forth). If the data is pivoted it may provide lists as required, but it will be difficult to incorporate other data elements aside from Student Name etc.

Answer 1

You can easily do this.

Here's your sample data, plus a calulated column that gives you a row number per student

DECLARE @t TABLE (StudentName varchar(20), ClassName varchar(20))
INSERT INTO @t values 
        ('Student1', 'Class1'),
        ('Student2', 'Class1'),
        ('Student3', 'Class1'),
        ('Student4', 'Class2'),
        ('Student5', 'Class2'),
        ('Student6', 'Class3'),
        ('Student7', 'Class3'),
        ('Student8', 'Class3'),
        ('Student9', 'Class3')
        
SELECT 
    StudentName, ClassName
    , RowN = ROW_NUMBER() OVER(PARTITION BY ClassName ORDER BY StudentName)
    FROM @t         

Now all you need to do is create a matrix, drag the Class as the column group and RowN as the row group and finally drag the student name to the data 'cell' and that's pretty much all you have to do.

Here's a 40 seconds gif that first shows the output from the query above, it then switches to the the report designer where I have already added a dataset containing the same query.

Right-click and "Open Link In New Tab/Window" if you want to make it bigger and easier to watch.