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 '10 at 06:53 AM in Default

Ian Roke gravatar image

Ian Roke
1.7k 29 33 34

(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 '10 at 08:27 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

+1 Interesting idea but a bit messy. I was hoping it would be simpler than that. I might as well just use CASE statements for the ones I want to see. Thanks for your reply I will leave it unanswered for now to see if anybody else fancies a try.
Mar 04 '10 at 11:48 AM Ian Roke
well if you know up front the column names, then it's easy, with no need for dynamic sql or multiple CASE
Mar 04 '10 at 11:51 AM Kev Riley ♦♦
I liked your solution for the dynamicness of it. ;-)
Mar 04 '10 at 12:03 PM Ian Roke
I just don't like dynamic sql...urgh!
Mar 04 '10 at 12:15 PM Kev Riley ♦♦
It works well enough which is all I need. Thanks Kev. One thing I would like it to do is output blank instead of NULL. Is this possible? I couldn't fathom it.
Mar 05 '10 at 05:42 AM Ian Roke
(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 '10 at 12:52 AM

Jeff Moden gravatar image

Jeff Moden
1.7k 2 8

(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 '10 at 05:59 PM

Dave Slee gravatar 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1933
x977
x76

asked: Mar 04 '10 at 06:53 AM

Seen: 2176 times

Last Updated: Mar 04 '10 at 06:53 AM