question

Ian Roke avatar image
Ian Roke asked

Can I PIVOT this without resorting to CASE statements?

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?

sql-server-2005t-sqlpivot
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

Here's a nasty way using dynamic sql

declare @cols NVARCHAR(2000)
declare @formattedcols NVARCHAR(2000)

select @formattedcols = COALESCE(@formattedcols + ',isnull([' + Name + '],'''') as [' + Name + ']',
                         'isnull([' + Name + '],'''') as [' + Name + ']'),
       @cols = COALESCE(@cols + ',[' + Name + ']',
                         '[' + Name + ']')                  
from sys.database_principals
where
    type = 'R'

declare @qry as NVARCHAR(4000)

set @qry = '
select
    username, '+ @formattedcols +'
from
(
SELECT 
    dp1.name as Username,
    dp.name as Rolename,
    case when drm.role_principal_id is not null then ''X'' else '''' end as userID

FROM 
    sys.database_role_members [drm]
JOIN sys.database_principals [dp] ON drm.role_principal_id = dp.principal_id
full JOIN sys.database_principals [dp1] on drm.member_principal_id = dp1.principal_id
where    dp1.name not like ''db_%''
) as sourcetable
PIVOT
(
    max(userid)
FOR rolename in ('+ @cols +')
) as PIVOTTABLE
'

execute (@qry)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered

You might actually want to reconsider using the CASE statements of a classic Cross-tab. Please see the following article for why. link text

For a small study on how to quickly and accurately write dynamic cross-tabs, please see the following article. link text

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave Slee avatar image
Dave Slee answered

You would have to know which roles you're concerned with before hand (which is always the case with PIVOT), but this should get you there:

SELECT [User], db_datareader, db_datawriter, db_owner
FROM (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 ) as roles
PIVOT ( count(Role)
FOR Role
IN ( db_datareader
, db_datawriter
, db_owner ) ) as role_table
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.