declare @ownerId int = ; declare @ownerType varchar(3) = ; with reasons as ( select reasonPath, case when nullif( r.parentReasonId, 0 ) is null and m.mfr_name is not null then replace( m.mfr_name, '/', '|' ) else 'General' end + '/' + replace( r.generalReason, '/', '|' ) topLineReasonPath from tbl_CRM_SaleReasons r with (nolock) left outer join tblManufacturers m with (nolock) on m.mfr_id = r.mfrId where ownerId = @ownerId and ownerType = @ownerType ), reasonPaths as ( select distinct replace( m.mfr_name, '/', '|' ) as reasonPath from tbl_CRM_SaleReasons r with (nolock) inner join tblManufacturers m with (nolock) on m.mfr_id = r.mfrId where nullif( r.parentReasonId, 0 ) is null and r.ownerId = @ownerId and r.ownerType = @ownerType union all select 'General' as reasonPath union all select distinct topLineReasonPath as reasonPath from reasons union all select distinct reasonPath from reasons where reasonPath != topLineReasonPath ) #forJSON( 'reasonPaths', attributes.datastore, 'reasonPath' )#