Can I PIVOT this without resorting to CASE statements?

Question

question

Ian Roke asked Mar 4, '10

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-2005 t-sql pivot

10 |1200

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

Answer 1 · 2010-03-04T08:27:06Z

Kev Riley answered Mar 4, '10

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

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

Answer 2 · 2010-03-07T00:52:19Z

Jeff Moden answered Mar 7, '10

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

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

Answer 3 · 2010-03-17T17:59:13Z

Dave Slee answered Mar 17, '10

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

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

question