SQL query Pivot Many to Many to Many Table

Tables are as follows:

Role

Id Name
1 Author


Object

Id Name
1 Blog
2 Post
3 User


Permission

Id Name
1 Create
2 Read
3 Update
4 Delete


RoleObjectPermission

RoleId ObjectId PermissionId
1 1 2
1 1 3
1 2 1
1 2 2
1 2 3
1 2 4


Desired query result for Role.Id = 1:

Object Create Read Update Delete
Blog 0 1 1 0
Post 1 1 1 1
User 0 0 0 0


I attempted to follow this site as well as this SO question, but those examples were only for a dynamic rows pivoted to columns and static columns pivoted to rows.

The query will only be used for one Role at a time. I need the query to handle new Objects and new Permissions

My SQL so far:

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @columns = N'';

SELECT
@columns += N', pi.'+QUOTENAME([PermissionName])
FROM
(SELECT [Name] AS [PermissionName]
FROM [dbo].[Permission] AS p
GROUP BY [Name]) AS x;

SET @sql = N'

SELECT [ObjectName], ' + STUFF(@columns, 1, 2, '') + '
FROM
(SELECT
oP.[Name] AS ObjectName,
(SELECT COUNT(rop.RoleID)
FROM [dbo].[RoleObjectsPermissions] rop, [dbo].[Object] o,
[dbo].[Role] r, [dbo].[Permission] p
WHERE
rop.RoleID = r.ID AND rop.ObjectID = o.ID
AND rop.PermissionID = p.ID
AND r.ID = rP.ID AND o.ID = oP.ID AND p.ID = pP.ID) AS [Quantity],
oP.[Name]
FROM
[dbo].[RoleObjectsPermissions] ropP,
[dbo].[Object] oP,
[dbo].[Role] rP,
[dbo].[Permission] pP
WHERE
ropP.RoleID = rP.ID AND ropP.ObjectID = oP.ID
AND ropP.PermissionID = pP.ID
AND rP.ID = 2) AS j
PIVOT (SUM(Quantity) FOR [Name] in
('+STUFF(REPLACE(@columns, ', pi.[', ',['), 1, 1, '')+')
) AS pi;';

EXEC sp_executesql @sql


EDIT 1:

I modified the table entries to provide the scenario where a role won't have any permissions on an object as well as partial permissions on another

EDIT 2:

This is the final @sql variable that gave me what i was after. I basically took Xedni's answer instead of inner joining all the relationship tables, i left joined a sub query around all the objects. very cool stuff!

@SQL =
concat
('
select
[Object],
', @Exp, '
from
(
select
[Object] = o.Name,
sub.PermissionName,
sub.PermissionId,
sub.RoleName
from dbo.Object o
left join (
select
ObjectId = rop.objectId,
PermissionName = p.name,
PermissionId = p.id,
RoleName = r.name
from
dbo.RoleObjectsPermissions rop
inner join dbo.Permission p
on rop.PermissionId = p.Id
inner join dbo.Role r
on rop.RoleId = r.Id
where r.Id = 1
) sub on sub.ObjectId = o.ID
) s
pivot (max(PermissionId) for PermissionName in (', @Fields, ')) p'
)


JOIN

1 Answer
1

/*****************************
Test Harness
*****************************/
if object_id('tempdb.dbo.#Role') is not null drop table #Role
create table #Role
(
Id int primary key,
Name nvarchar(128)
)

if object_id('tempdb.dbo.#Object') is not null drop table #Object
create table #Object
(
Id int primary key,
Name nvarchar(128)
)

if object_id('tempdb.dbo.#Permission') is not null drop table #Permission
create table #Permission
(
Id int primary key,
Name nvarchar(128)
)

if object_id('tempdb.dbo.#RoleObjectPermission') is not null drop table #RoleObjectPermission
create table #RoleObjectPermission
(
RoleId int,
ObjectId int,
PermissionId int

)

insert into #Role values (1, 'Admin')
insert into #object values (1, 'Blog')
insert into #Permission
values
(1, 'Create'),
(2, 'Read'),
(3, 'Update'),
(4, 'Delete')
insert into #RoleObjectPermission
values
(1, 1, 1),
--(1, 1, 2),
(1, 1, 3),
(1, 1, 4)

/*********************
Dynamic Pivot
*********************/
declare
@Fields nvarchar(max),
@Exp nvarchar(max),
@SQL nvarchar(max)

select
-- list of columns
@Fields =
stuff
(
(
select concat(',', quotename(name))
from #Permission
order by Id
for xml path('')
), 1, 1, ''
),
-- List of statements which evaluate to 0 if null
@Exp =
stuff
(
(
select concat(', ', quotename(name), ' = iif(', quotename(name), ' is null, 0, 1)')
from #Permission
order by Id
for xml path('')
), 1, 1, ''
),
@SQL =
concat
('
select
[Object],
', @Exp, '
from
(
select
[Object] = o.Name,
PermissionName = p.name,
PermissionId = p.id,
RoleName = r.Name
from #RoleObjectPermission rop
inner join #Object o
on rop.ObjectId = o.Id
inner join #Permission p
on rop.PermissionId = p.Id
inner join #Role r
on rop.RoleId = r.Id
where r.Id = 1
) s
pivot (max(PermissionId) for PermissionName in (', @Fields, ')) p'
)

exec sp_executesql @SQL


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.