In my SQL database, I have a one-to-many relationship, something like this:
Teacher Student John Alex John Mike John Sean Bob Jack Gary George Gary Paul
I'd like to display a table listing each teacher, with their students as a comma-delimited list, like this:
Teacher Students John Alex, Mike, Sean Bob Jack Gary George, Paul
This question describes how to do this on the SQL Server end, but is there a way to do this on the SSRS side of things?
Add a grouping on teacher, and use the .net Join to append the detail rows
Join is demonstrated for multi-value parameters in BOL... so in theory it can be used for the result dataset
I've been researching this a bit, and have come across another work-around.
Create a custom function, such as GetStudentList(TeacherId As Integer, ConnectionString As String), which is intended to return the list of students based on the specified teacher.
This function can then be written to open a connection to the database, run a query, process the results, and then return them. But that means opening the connection and running the query for every row, which seems like a lot of overhead for this type of formatting (not to mention the need to pass in a Connection String).
This is based largely on an Experts' Exchange article.