btamulis 10/13/2021 10:39:50 AM

GP Dynamics Security SQL View - Consolidated

Okay - I would like a simple 'report' which shows one record for every GP user/Company combination - however I need to include a longer stringer of the first 7 or 8 Security Roles.

This SQL script has one row for every user/company/role.

use DYNAMICS
SELECT
['User Master'].USERID AS 'UserID'
,['User Master'].USERNAME AS 'Username'
----,['User Master'].USRCLASS AS 'UserClass'
----,ISNULL(['Class Master'].DSCRIPTN, '') AS 'User Class Description'
,ISNULL(['Company Master'].INTERID, '') AS 'CompanyID'
,ISNULL(['Company Master'].CMPNYNAM, '') AS 'CompanyName'
,left(ISNULL(['Security Assignment User Role'].SECURITYROLEID,  ''), 11) AS 'SecurityRoleID'
,case when ['Security Assignment User Role'].SECURITYROLEID='POWERUSER' then 'Yes' else 'No' end as PowerUser
-----,left(ISNULL(['Security Roles Master'].SECURITYROLENAME, ''), 11) AS 'SecurityRoleName'
FROM
SY01400 AS ['User Master']
LEFT JOIN
SY40400 AS ['Class Master']
ON ['Class Master'].USRCLASS = ['User Master'].USRCLASS
LEFT JOIN
SY60100 AS ['User-Company Access']
ON ['User-Company Access'].USERID = ['User Master'].USERID
LEFT JOIN
SY10500 AS ['Security Assignment User Role']
ON ['Security Assignment User Role'].CMPANYID = ['User-Company Access'].CMPANYID
AND ['Security Assignment User Role'].USERID = ['User-Company Access'].USERID
LEFT JOIN
SY09100 AS ['Security Roles Master']
ON ['Security Roles Master'].SECURITYROLEID = ['Security Assignment User Role'].SECURITYROLEID
LEFT JOIN
SY01500 AS ['Company Master']
ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID
GO

It returns something like this: (desired output would be 4 rows - one for each user/company with one additional column 'PrimaryUse')

I would like to return one row for every user/company with a single column that strings together 7 or 8 roles into a singular piece of data. Basically pivot the rows by concatenating the roles into a single string

Anybody do this before? 

Version: All
Section: Dynamics GP, SQL Scripts


Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3