select fieldDomain from tbl_WRK_Forms with (nolock) where ownerId = and ownerType = and formId = declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @formId int = ; declare @submissionId int = ; with fields as ( select fs.formId, fs.fieldSetName, fs.fieldSetRank, f.fieldId, f.name fieldName, f.label fieldLabel, rtrim( f.category ) fieldCategory, f.sortRank , f.config , json_query( case when f.config = 'cfquery=lookup' then ( select fl.selectionValue as [value], fl.selectionLabel as [text], fl.sortRank from tbl_#_fieldDomain#_FieldLookups fl with (nolock) where fl.ownerId = f.ownerId and fl.ownerType = f.ownerType and fl.fieldName = f.name order by fl.sortRank, [text] for json auto ) else ( null ) end ) as lookupValues from tbl_WRK_FormFieldSets fs inner join tbl_WRK_FormFieldSetFields fsf on fsf.ownerId = fs.ownerId and fsf.ownerType = fs.ownerType and fsf.fieldSetId = fs.fieldSetId inner join tbl_#_fieldDomain#_Fields f on f.ownerId = fsf.ownerId and f.ownerType = fsf.ownerType and f.fieldId = fsf.fieldId where fs.ownerId = @ownerId and fs.ownerType = @ownerType and fs.formId = @formId ), submission as ( select fs.formId, f.fieldId, f.name fieldName, f.label fieldLabel, fv.data submissionValue, f.sortRank, rtrim( f.category ) fieldCategory from tbl_#_fieldDomain#_FieldValues fv with (nolock) inner join tbl_#_fieldDomain#_Fields f with (nolock) on f.fieldId = fv.fieldId inner join tbl_WRK_FormSubmissions fs with (nolock) on fs.submissionId = fv.partnerId and 'WFS' = fv.partnerType where fs.submissionId = @submissionId and fs.formId = @formId and fs.ownerId = @ownerId and fs.ownerType = @ownerType ), fieldsAndSubmission as ( select coalesce( s.fieldId, f.fieldId ) fieldId, coalesce( s.fieldName, f.fieldName ) fieldName, coalesce( s.fieldCategory, f.fieldCategory) fieldCategory, coalesce( s.fieldLabel,f.fieldLabel ) fieldLabel, s.submissionValue, coalesce( s.sortRank, f.sortRank ) fieldRank , f.config, f.lookupValues from fields f full outer join submission s on s.formId = f.formId and s.fieldId = f.fieldId ), result as ( select *, fieldCategory + '_' + fieldName qualifiedField from fieldsAndSubmission ) #forJSON( 'result', attributes.datastore, 'fieldRank' )#