SQL CTE To Get Hierarchy as concatenated result

You can try to use a filter in first of cte

Query 1:

 with cte as (
    select Person, 
        cast(AssignedTo as nvarchar(max)) caption, 
        AssignedTo,
        1 rn
    from tblHierarchy 
    WHERE AssignedTo = 'No More Level'
    union all 
    select t.Person, 
        cast(t.AssignedTo + '|' + c.caption as nvarchar(max)), 
        t.AssignedTo,
        rn +1
    from cte c
    inner join tblHierarchy t on t.AssignedTo = c.Person
)
select concat('|',caption,'|')
from cte 
order by rn desc,concat('|',caption,'|') 
option (maxrecursion 0)

Results:

Seems this is what you're after. you need to use an rCTE to iterate through your dataset, providing details for both the "original" user and their "parent". Then you need to get the "Top 1 per group" for each user, based on the maximum level, as you'll get 1 row per level for a user otherwise. Then, finally, you can filter on the row number:

WITH rCTE AS(
    SELECT H.Person,
           H.AssignedTo,
           H.Person AS NextPerson,
           H.AssignedTo AS NextAssignedTo,
           CONVERT(nvarchar(4000),CONCAT('|',H.AssignedTo,'|')) AS ExpectedResult,
           1 AS Level
    FROM dbo.tblHierarchy H
    UNION ALL      
    SELECT r.Person,
           r.AssignedTo,
           H.Person AS NextPerson,
           H.AssignedTo AS NextAssignedTo,
           CONVERT(nvarchar(4000),CONCAT(r.Expectedresult,H.AssignedTo,'|')),
           Level + 1
    FROM dbo.tblHierarchy H
         JOIN rCTE r ON r.NextAssignedTo = H.Person),
RNs AS(
    SELECT r.Person,
           r.AssignedTo,
           r.ExpectedResult,
           ROW_NUMBER() OVER (PARTITION BY r.Person ORDER BY r.level DESC) AS RN
    FROM rCTE r)
SELECT RN.Person,
       RN.AssignedTo,
       RN.ExpectedResult
FROM RNs RN
WHERE RN = 1
ORDER BY RN.Person;

Note that that the ordering isn't what is in your expected results due to your data type choice. Person is an nvarchar and so the value '10' is lower than the value '2', and so all person's with a value starting with '1' will be sorted first, then '2', then '3', etc.

Maybe this is what you are after ?

with Hierarchy(Person, AssignedTo, Result) as
( select m.Person, m.AssignedTo, convert(varchar(max), m.Person)
  from   tblHierarchy m
    left join tblHierarchy m1 on m.AssignedTo = m1.Person
  where m.AssignedTo = 'No More Level'
  union all
  select d.Person, d.AssignedTo, convert(varchar(max), s.Result + '|' + d.Person)
  from   Hierarchy s
    inner join tblHierarchy d on s.Person = d.AssignedTo
)
select s.Person, s.AssignedTo, s.Result
from   Hierarchy s
order by case when isnumeric(s.Person) = 1 then convert(int, s.Person) else 99999 end
option (maxrecursion 0)

Try it in this DBFiddle

The result is

Person  AssignedTo      Result
1       10              CEO|10000|1000|100|10|1
2       20              CEO|20000|2000|200|20|2
3       30              CEO|30000|3000|300|30|3
10      100             CEO|10000|1000|100|10
20      200             CEO|20000|2000|200|20
30      300             CEO|30000|3000|300|30
100     1000            CEO|10000|1000|100
200     2000            CEO|20000|2000|200
300     3000            CEO|30000|3000|300
1000    10000           CEO|10000|1000
2000    20000           CEO|20000|2000
3000    30000           CEO|30000|3000
10000   CEO             CEO|10000
20000   CEO             CEO|20000
30000   CEO             CEO|30000
CEO     No More Level   CEO