SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[tblHierarchy](
    [Person] [nvarchar](50) NULL,
    [AssignedTo] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'1', N'10')
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'2', N'20')
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'3', N'30')
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'10', N'100')
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'20', N'200')
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'30', N'300')
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'100', N'1000')
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'200', N'2000')
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'300', N'3000')
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'1000', N'10000')
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'2000', N'20000')
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'3000', N'30000')
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'10000', N'CEO')
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'20000', N'CEO')
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'30000', N'CEO')
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'CEO', N'No More Level')

select * from tblHierarchy

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

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