declare @ownerId int = ; declare @ownerType varchar(3) = ; with rawData as ( select '#_domain#' as domain, f.fieldId, upper( rtrim( ltrim( f.name ))) name, upper( rtrim( ltrim( f.category ))) category, fl.selectionValue as [value], fl.selectionLabel as [text], fl.sortRank, sourceFieldId [sourceFieldId], sourceFieldOp [sourceFieldOp], sourceFieldValue [sourceFieldValue] cast( null as int ) sourceFieldId, cast( null as varchar) sourceFieldOp, cast( null as varchar ) sourceFieldValue from tbl_#_domain#_Fields f with (nolock) inner join tbl_#_domain#_FieldLookups fl with (nolock) on fl.ownerId = f.ownerId and fl.ownerType = f.ownerType and fl.fieldName = f.name left outer join tbl_WRK_Conditions c with (nolock) on c.ownerId = fl.ownerId and c.ownerType = fl.ownerType and c.targetType = 'L' and c.targetKey = cast( f.fieldId as varchar ) + ':' + fl.selectionValue where f.ownerId = @ownerId and f.ownerType = @ownerType and f.fieldId = ) , data as ( select *, category + '_' + name as fieldName from rawData where 1 = 1 and category = ) #forJSON( 'data', attributes.datastore, 'sortRank,value' )#