SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table ApplicationRoles(
SchemaId nvarchar(50),
RoleName nvarchar(50),
[create] bit not null default 0,
[read] bit not null default 0,
[update] bit not null default 0,
[delete] bit not null default 0
)
create table commonformsschema(
SchemaId nvarchar(50),
Tenant nvarchar(50)
)
insert into ApplicationRoles(SchemaId,RoleName,[create],[read],[update],[delete])
values('0240fb78-a006-4736-9089-6257fbc58f57','Senior Construction Manager','true','true','true','true'),
('09a1efbd-3ae7-4ae2-ad0e-cef1645b743d','Senior Construction Manager','true','false','true','true'),
('0a0015b2-c04c-4b2f-a71f-8adecc55a444','Senior Admin Manager','true','true','false','true'),
('7a9a2521-45fb-4c9d-b7d9-e41927118119','Project Manager','true','true','false','true'),
('ca5d8707-fee8-4a5b-ad61-813785e32575','Project Manager','true','true','false','true')
;
5 rows affected

insert into commonformsschema(SchemaId,Tenant)
values('0240fb78-a006-4736-9089-6257fbc58f57','test'),
('09a1efbd-3ae7-4ae2-ad0e-cef1645b743d','test'),
('0a0015b2-c04c-4b2f-a71f-8adecc55a444','All'),
('7a9a2521-45fb-4c9d-b7d9-e41927118119','test'),
('ca5d8707-fee8-4a5b-ad61-813785e32575','test')
;
5 rows affected

select fs1.SchemaId,JSON_QUERY(ar.newJson) as roles 
from commonformsschema fs1 
join 
ApplicationRoles ar1 
on ar1.SchemaId= fs1.SchemaId 
CROSS APPLY (SELECT (SELECT RoleName AS [role],[create] AS [permissions.create],[read] 
AS [permissions.read],[update] AS [permissions.update],[delete] AS [permissions.delete] FROM ApplicationRoles ar WHERE fs1.SchemaId = ar1.SchemaId 
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS newJson) ar 
where fs1.Tenant in ('test', 'All') and ar1.RoleName in 
('Project Manager', 'Read') 


SchemaId
roles


7a9a2521-45fb-4c9d-b7d9-e41927118119
{"role":"Senior Construction Manager","permissions":{"create":true,"read":true,"update":true,"delete":true}}

ca5d8707-fee8-4a5b-ad61-813785e32575
{"role":"Senior Construction Manager","permissions":{"create":true,"read":true,"update":true,"delete":true}}

SchemaId	roles
7a9a2521-45fb-4c9d-b7d9-e41927118119	{"role":"Senior Construction Manager","permissions":{"create":true,"read":true,"update":true,"delete":true}}
ca5d8707-fee8-4a5b-ad61-813785e32575	{"role":"Senior Construction Manager","permissions":{"create":true,"read":true,"update":true,"delete":true}}