declare @ownerId int = ; declare @ownerType varchar(3) = ; with fieldLookups as ( union all select jobFunctionId as caJobFunction, jobFunctionName as caJobFunctionText, sortRank, 'OPR' as partnerType from tbl_OPR_JobFunctions with (nolock) where ownerId = @ownerId and ownerType = @ownerType select cast( selectionValue as int ) as caJobFunction, selectionLabel as caJobFunctionText, sortRank, case when fieldName = 'CDRJobFunction' then 'CDR' when fieldName = 'BPLJobFunction' then 'BPL' else null end as partnerType from tbl_FSPRO_FieldLookups rr with (nolock) where rr.ownerId = @ownerId and rr.ownerType = @ownerType and rr.fieldName = ) , jobFunctions as ( select caJobFunction, caJobFunctionText, partnerType, sortRank as rawSortRank, row_number() over( order by partnerType, sortRank, caJobFunctionText ) as sortRank from fieldLookups ) #forJSON( 'jobFunctions', attributes.datastore, 'sortRank' )#