I have the following code querying Users and Roles in a database and I would like to pivot it by user and have Roles across the top with an 'X'
showing which roles the users have set up.
SELECT dp1.name [User],
dp.name [Role]
FROM sys.database_role_members [drm]
JOIN sys.database_principals [dp] ON drm.role_principal_id = dp.principal_id
JOIN sys.database_principals [dp1] on drm.member_principal_id = dp1.principal_id
Can I do this with a PIVOT statement or do I have to resort to several CASE statements?