declare @ownerId int = ; declare @ownerType varchar(3) = ; with groupList as ( SELECT g.groupId, g.groupName, g.groupTypeCd, coalesce( c.categoryId, 0 ) as categoryId, coalesce(c.category,'Uncategorized Groups') AS category, '##' + replace( g.modVisibility, ',', ';##' ) + ';' as modVisibility, case when rdg.groupId is not null then 'Y' else 'N' end as reportDistributionGroup FROM tbl_SEC_Groups g with (nolock) LEFT OUTER JOIN tbl_SEC_Categories c with (nolock) ON c.categoryId= g.categoryId and c.ownerId = g.ownerId and c.fsl_tablecode = g.fsl_tablecode left outer join tbl_SYS_ReportDistributionGroups rdg on rdg.ownerId = g.ownerId and rdg.ownerType = g.fsl_tablecode and rdg.groupId= g.groupId WHERE g.ownerID = @ownerId AND g.fsl_tableCode = @ownerType ) , sysGroups as ( SELECT groupId, groupName, groupTypeCd, 0 as categoryId, case when GroupTypeCD = 'MPR' or brokerRole = 1 then 'Primary Broker Contacts' else case when GroupTypeCD = 'MRR' then 'General Broker Staff Responsibilities' else case when GroupTypeCD = 'SME' then 'Subject Matter Expert' end end end as category, '##' + replace( modVisibility, ',', ';##' ) + ';' as modVisibility, 'N' as reportDistributionGroup FROM tbl_SEC_Groups with (nolock) WHERE groupTypeCd in ('MPR','MRR','SME') or ( BrokerRole = 1 AND OwnerID = @ownerId AND FSL_TableCode = @ownerType ) ) , groups as ( select * from groupList union all select * from sysGroups ) #forJSON( 'groups', attributes.dataStore, 'groupName' )#