SQL CTE To Get Hierarchy as concatenated result

March 24, 2022

In the below example I am trying to retrieve the complete reporting structure of each employees.

For example 1 is reporting to 10.
10 is reporting to 100.
100 is reporting to 1000.
1000 is reporting to 10000.
10000 is reporting to CEO
CEO is reporting to No More Level

so for person 1 the complete reporting structure shoule be |10|100|1000|10000|CEO|No More Level|

Like this I require the complete reporting structure for all persons.

My Data

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

Expected Result

|10|100|1000|10000|CEO|No More Level|
|20|200|2000|20000|CEO|No More Level|
|30|300|3000|30000|CEO|No More Level|
|100|1000|10000|CEO|No More Level|
|200|2000|20000|CEO|No More Level|
|300|3000|30000|CEO|No More Level|
|1000|10000|CEO|No More Level|
|2000|20000|CEO|No More Level|
|3000|30000|CEO|No More Level|
|10000|CEO|No More Level|
|20000|CEO|No More Level|
|30000|CEO|No More Level|
|CEO|No More Level|
|CEO|No More Level|
|CEO|No More Level|
|No More Level|

I tried this but it is not working.
SQLFiddleLink

Insert Table statement

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

My SQL Attempt

    with cte as (
    select Person, cast(Person as nvarchar(max)) caption, 
    AssignedTo from tblHierarchy 
    union all 
    select t.Person, cast('|'+ c.caption + '|' + t.Person as nvarchar(max))+'|', t.AssignedTo
    from cte c
    inner join tblHierarchy t on t.AssignedTo = c.Person
)
select Person,caption from cte order by Person

>Solution :

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.