How to combine results from 4 rows into 1?

Question

Post reply

How to combine results from 4 rows into 1?

Budd

Hall of Fame

Points: 3772
More actions
October 8, 2015 at 11:02 am

#308780

How can I get the results of this query>>
SELECT
RoleName = pr.name
,RoleType = pr.type_desc
,PermissionType = pe.state_desc
,Permission = pe.permission_name
,ObjectName = s.name + '.' + o.name
,ObjectType = o.type_desc
,Created = o.create_date
,Altered = o.modify_date
FROM sys.database_principals AS pr
JOIN sys.database_permissions pe ON pe.grantee_principal_id=pr.principal_id
RIGHT JOIN sys.objects o ON pe.major_id=o.object_id
JOIN sys.schemas s ON o.schema_id=s.schema_id
WHERE is_ms_shipped=0
AND o.TYPE IN ('FN','P','U','V','TF')
ORDER BY CASE WHEN pr.name IS NULL THEN 0 ELSE 1 END
,o.type
,o.name
,pr.name
,pe.permission_name
WHICH RESULTS IN THIS>>
RoleNameRoleTypePermissionType PermissionObjectNameObjectType
publicDATABASE_ROLEGRANTDELETEdbo.AgencyUSER_TABLE
publicDATABASE_ROLEGRANTINSERTdbo.AgencyUSER_TABLE
publicDATABASE_ROLEGRANTSELECTdbo.AgencyUSER_TABLE
publicDATABASE_ROLEGRANTUPDATEdbo.AgencyUSER_TABLE
TO INSTEAD LOOK LIKE THIS >>
RoleNameRoleTypePermissionType Permission ObjectNameObjectType
publicDATABASE_ROLEGRANTDELETE,INSERT,SELECT,UPDATEdbo.AgencyUSER_TABLE
Thanks for any help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MadAdmin SSChampion Points: 11261 More actions · Answer 1

Concatenate with FOR XML PATH.

http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

Here is another article which has all the information rolled into a nice tidy package. http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

Quick solution

😎

SELECT

RoleName = max(pr.name )

,RoleType = max(pr.type_desc )

,perms = max( X.PERMS )

,ObjectName = max(s.name + '.' + o.name )

,ObjectType = max(o.type_desc )

,Created = max(o.create_date )

,Altered = max(o.modify_date )

FROM sys.database_principals AS pr

JOIN sys.database_permissions pe ON pe.grantee_principal_id=pr.principal_id

RIGHT JOIN sys.objects o ON pe.major_id=o.object_id

JOIN sys.schemas s ON o.schema_id=s.schema_id

CROSS APPLY

(SELECT STUFF ( (SELECT NCHAR(44) + spe.permission_name

FROM sys.database_permissions spe

WHERE pe.grantee_principal_id = spe.grantee_principal_id

FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(512)')

,1,1,'')) AS X(PERMS)

WHERE is_ms_shipped=0

AND o.TYPE IN ('FN','P','U','V','TF')

AND pr.name IS NOT NULL

GROUP BY pr.name

,pr.type_desc

,s.name + '.' + o.name

,pe.grantee_principal_id

,o.type

,o.name

,X.PERMS

ORDER BY CASE WHEN pr.name IS NULL THEN 0 ELSE 1 END

,o.type

,o.name

,pr.name;

Budd Hall of Fame Points: 3772 More actions · Answer 4

OUTSTANDING !!

ALL of this is VERY helpful,

Thank you all