您只需在cte中添加第二个联合：

with cte as (

select OrgUnitId, ParentOrgUnitId, PersonId, isExcluded , 0 as level_num
from #OrgList
where ParentOrgUnitId is null
UNION ALL
select o.OrgUnitId, o.ParentOrgUnitId, o.PersonId, o.isExcluded , level_num+1 as level_num
from #OrgList o
join cte on o.ParentOrgUnitId=cte.OrgUnitId
where cte.isExcluded = 0 
UNION ALL
select o.OrgUnitId, cte.ParentOrgUnitId, o.PersonId, o.isExcluded , level_num as level_num
from #OrgList o
join cte on o.ParentOrgUnitId=cte.OrgUnitId
where cte.isExcluded = 1

)
select * from cte

我的方法：
使用排除计数器扩展初始cte( ExclusionCount )，计算从根节点到叶节点的排除节点数。
添加另一个递归cte来构造上行路径( cte_upwards )对于每个叶节点。现在减小在初始cte中添加的计数器。
使用 cross apply 选择第一个向上路径达到0 exlusion count的节点。
解决方案：

with cte as                                                     -- initial CTE
(
    select  OrgUnitId,
            ParentOrgUnitId,
            PersonId,
            IsExcluded,
            convert(int, IsExcluded) as 'ExclusionCount',       -- new counter
            0 as 'level_num'
    from #OrgList
    where ParentOrgUnitId is null
        union all
    select  o.OrgUnitId,
            o.ParentOrgUnitId,
            o.PersonId,
            o.IsExcluded,
            cte.ExclusionCount + convert(int, o.isExcluded),    -- increment counter
            cte.level_num + 1
    from #OrgList o
    join cte on o.ParentOrgUnitId = cte.OrgUnitId
),
cte_upwards as
(
    select  cte.OrgUnitId,
            cte.ParentOrgUnitId as 'NewParentOrgUnitId',
            cte.IsExcluded,
            cte.ExclusionCount,
            cte.level_num
    from cte
    where cte.ParentOrgUnitId is not null   -- only leaf nodes (not a root)
      and not exists (  select top 1 'x'    -- only leaf nodes (not an intermediate node)
                        from cte cp
                        where cp.ParentOrgUnitId = cte.OrgUnitId )
        union all
    select  cte_upwards.OrgUnitId,
            cte.ParentOrgUnitId,
            cte.IsExcluded,
            cte_upwards.ExclusionCount - cte.IsExcluded,    -- decrement counter
            cte.level_num
    from cte_upwards
    join cte
        on cte.OrgUnitId = cte_upwards.NewParentOrgUnitId
)
select  cte.OrgUnitId,
        cte.ParentOrgUnitId,
        cte.IsExcluded,
        x.NewParentOrgUnitId,
        coalesce(x.NewParentOrgUnitId, cte.ParentOrgUnitId) as 'Recalculated'
from cte
outer apply (   select top 1 cu.NewParentOrgUnitId
                from cte_upwards cu
                where cu.OrgUnitId = cte.OrgUnitId
                  and cu.ExclusionCount = 0         -- node without excluded parent nodes
                order by cu.level_num desc ) x      -- select lowest node in upwards path
order by cte.OrgUnitId;

结果：

OrgUnitId   ParentOrgUnitId IsExcluded NewParentOrgUnitId Recalculated
----------- --------------- ---------- ------------------ ------------
1           NULL            0          NULL               NULL
2           1               0          NULL               1
3           1               0          NULL               1
4           2               1          NULL               2
5           2               0          2                  2
6           3               0          3                  3
7           4               0          2                  2
8           4               0          2                  2
9           4               0          2                  2
10          4               0          2                  2
11          4               1          NULL               4
12          11              0          2                  2

我添加了一个virtualparentorgunitid，其中包含考虑了排除节点的父节点。我还添加了一个计数器virtualInstance，它将报告此节点与其虚拟父节点之间的实际跃点数。
如果未排除，virtualparentorgunitid将使用父级的id，否则它将使用父级的virtualparentorgunitid，这允许多个级别的链接。

DROP TABLE IF EXISTS #OrgList
CREATE TABLE #OrgList (
    OrgUnitId int,
    ParentOrgUnitId int,
    PersonId int,
    isExcluded bit
);

INSERT INTO #OrgList(OrgUnitId, ParentOrgUnitId, PersonId, isExcluded) VALUES
    (1, NULL, 100, 0), (2,1, 101, 0), (3,1,102,0), (4,2,103,1), (5,2,104,0), (6,3,105,0), (7,4,106,0), (8,4,107,0), (9,4,108,0), (10,4,109,0), (11,4,110,1), (12,11,111,0)

DROP TABLE IF EXISTS #Excludes
CREATE Table #Excludes (
    OrgUnitId int
);

INSERT INTO #Excludes VALUES (4), (11);

with cte as (
select OrgUnitId, ParentOrgUnitId, ParentOrgUnitId VirtualParentOrgUnitId, 1 as VirtualDistance , PersonId, isExcluded , 0 as level_num
from #OrgList
where ParentOrgUnitId is null
UNION ALL
select o.OrgUnitId, o.ParentOrgUnitId, IIF(o.ParentOrgUnitId IN (SELECT OrgUnitId FROM #Excludes),cte.VirtualParentOrgUnitId,  o.ParentOrgUnitId ), IIF(o.ParentOrgUnitId IN (SELECT OrgUnitId FROM #Excludes),VirtualDistance + 1,  1 ), o.PersonId, o.isExcluded , level_num+1 as level_num
from #OrgList o
join cte on o.ParentOrgUnitId=cte.OrgUnitId

)
select * from cte

结果如下：

OrgUnitId   ParentOrgUnitId VirtualParentOrgUnitId VirtualDistance PersonId    isExcluded level_num
----------- --------------- ---------------------- --------------- ----------- ---------- -----------
1           NULL            NULL                   0               100         0          0
2           1               1                      0               101         0          1
3           1               1                      0               102         0          1
6           3               3                      0               105         0          2
4           2               2                      0               103         1          2
5           2               2                      0               104         0          2
7           4               2                      1               106         0          3
8           4               2                      1               107         0          3
9           4               2                      1               108         0          3
10          4               2                      1               109         0          3
11          4               2                      1               110         1          3
12          11              2                      2               111         0          4

;
with cte as (
select OrgUnitId, ParentOrgUnitId, PersonId, isExcluded , 0 as level_num, 0 as level_after_exclusions, 
cast(',' as varchar(max)) + case isExcluded when 1 then cast(OrgUnitId as varchar(20)) else '' end as excludedmembers,
case isExcluded when 1 then ParentOrgUnitId end as newParentId
from #OrgList
where ParentOrgUnitId is null
UNION ALL
select o.OrgUnitId, o.ParentOrgUnitId, o.PersonId, o.isExcluded , level_num + 1, level_after_exclusions + case o.isExcluded when 1 then 0 else 1 end, 
excludedmembers + case o.isExcluded when 1 then cast(o.OrgUnitId as varchar(20))+',' else '' end,
case when excludedmembers like '%,'+cast(o.ParentOrgUnitId as varchar(20))+',%' then newParentId else o.ParentOrgUnitId  end 

from #OrgList o
join cte on o.ParentOrgUnitId=cte.OrgUnitId
)
select *, level_num - level_after_exclusions as shiftbylevels
from cte