SQL Pivot Table for Many-To-Many Relationship for use in SSRS

Question

Using SQL Server 2008, I am trying to figure out how to create a query that returns a pivot table with a standard many-to-many relationship. This relationship defines which users belong to which roles and I want the table to list the user name along the side and the role names across the top. The ultimate goal is to have this output in SQL Server Reporting Services, so it doesn't matter if SQL server generates the pivot results, or SSRS generates the results (is one method better than the other?). Here is my sample schema:

Users Table:

• UserID
• UserName

Rights Table:

• RightID
• RightName

RightsMembership Table:

• UserID
• RightID

I want the following output as a report in SSRS. Any help is appreciated.

       RightOne   RightTwo   RightThree   RightFour
jdoe       X          X
mjane      X                       X
ssmith                X            X           X

FYI: Roles can be added, so I would prefer not to have to hard code the role name or count in the query.

Answer 1

I Don't Know about the Pivot Tables but you can achieve this in the SSRS as below

Select UserName,
           RightName
From users u INNER JOIN RightMembership rm on rm.UserID = u.UserID
             INNER JOIN Rights r on rm.RightID = r.RightID

Use this query as the Stored Procedure or Query and the Order By Accordingly and

Create the Dataset and Datasource

Insert matrix in the report

In the rows select the UserName field From the Created Dataset In the columns select the RightName field from the created Dataset In the Data use this Expression below then you will get the desired output =IIF(Fields!UserName.Value = nothing,nothing,"X") As the Data in the matrix