All geek questions in one place

SQL Pivot Table for Many-to-Many Relationships for Use in SSRS

Using SQL Server 2008, I'm trying to figure out how to create a query that returns a pivot table with a standard many-to-many relationship. This relationship determines which users belong to those roles, and I want the table to have a username on the side and role names at the top. The ultimate goal is to get this result in SQL Server Reporting Services, so it doesn't matter if SQL Server generates summary results or SSRS generates results (is one method better than the other?). Here is my sample diagram:

Users table:

  • User ID
  • Username

Rights Table:

  • Rightid
  • Rightname

RightsMembership Table:

  • User ID
  • Rightid

I want to get the following result as a report in SSRS. Any help is appreciated.

RightOne RightTwo RightThree RightFour jdoe XX mjane XX ssmith XXX

FYI: Roles can be added, so I would prefer not to require a hard code for the role name or count in the request.

+4
source share
1 answer

I do not know about pivot tables, but you can achieve this in SSRS as shown 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 a stored procedure or query and default order and

Create a dataset and data source

Insert matrix into report

In the rows, select the UserName field from the created dataset. In the columns, select the RightName field from the created dataset. In "Data" use this expression below, after which you will get the desired result = IIF (Fields! UserName.Value = nothing, nothing, "X") Since the data in the matrix

+5
source

Source: https://habr.com/ru/post/1399871/

More articles:


All Articles