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_NoSaleReasons r with (nolock) left outer join tblManufacturers m with (nolock) on m.mfr_id = r.mfrId where ownerId = @ownerId and ownerType = @ownerType and reasonType = 'GENERAL' ), reasonPaths_list as ( select distinct replace( m.mfr_name, '/', '|' ) as reasonPath from tbl_CRM_NoSaleReasons 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 and r.reasonType = 'GENERAL' 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 ), reasonPaths as ( select reasonPath from reasonPaths_list where reasonPath is not null) #forJSON( 'reasonPaths', attributes.datastore, 'reasonPath' )#