x

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?

more ▼

asked Mar 04, 2010 at 06:53 AM in Default

avatar image

Ian Roke
1.7k 32 35 38

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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)

more ▼

answered Mar 04, 2010 at 08:27 AM

avatar image

Kev Riley ♦♦
64.2k 48 62 81

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Mar 07, 2010 at 12:52 AM

avatar image

Jeff Moden
1.9k 3 7 12

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Mar 17, 2010 at 05:59 PM

avatar image

Dave Slee
1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2017
x1069
x104

asked: Mar 04, 2010 at 06:53 AM

Seen: 2666 times

Last Updated: Mar 04, 2010 at 06:53 AM

Copyright 2016 Redgate Software. Privacy Policy