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

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.

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.

Use "dynamic SQL" to generate the pivot values based on the data in your table, then excute that generated pivot query e.g:

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(Class)
                      FROM students
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = N'WITH CTE AS (
    SELECT Name, Class, ROW_NUMBER() OVER (PARTITION BY Class ORDER BY Name) AS RowNum
    FROM students
)
SELECT ' + @cols + '
FROM CTE
PIVOT (
    MAX(Name)
    FOR Class IN (' + @cols + ')
) AS pvt
ORDER BY RowNum;';

EXECUTE sp_executesql @query;

For SSRS, use the dynamic SQL inside in a stored procedure and then use that stored procedure as the data source for your SSRS report. i.e. Create a new stored procedure in your database and paste the dynamic SQL code into it. Make sure to replace [YourTable] with the real name.

CREATE PROCEDURE [dbo].[usp_YourProcedureName]
AS
BEGIN
    DECLARE @cols AS NVARCHAR(MAX),
            @query AS NVARCHAR(MAX);

    SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(Class)
                          FROM students
                          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

    SET @query = N'WITH CTE AS (
        SELECT Name, Class, ROW_NUMBER() OVER (PARTITION BY Class ORDER BY Name) AS RowNum
        FROM students
    )
    SELECT ' + @cols + '
    FROM CTE
    PIVOT (
        MAX(Name)
        FOR Class IN (' + @cols + ')
    ) AS pvt
    ORDER BY RowNum;';

    EXECUTE sp_executesql @query;
END

• In your SSRS report, create a new dataset and set its data source to the stored procedure you just created.
• Use this dataset to populate a matrix in your report. Set the Student column as the row group and the Class columns as the column group.

NOTE

If you have a large number of columns generated dynamically, it can cause width issues in your SSRS report. One way to handle this is to set the CanGrow property of the column to True and the CanShrink property to False. This will allow the column to grow in width to accommodate the data.

Another way to handle this is to set the LayoutDirection property of the matrix to Vertical. This will cause the matrix to grow vertically instead of horizontally, which can help with width issues.

Perhaps also try adjusting the size of the font and the column width to fit more columns on the page.

fiddle