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
CodePudding user response：
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)
CodePudding user response：
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.
CodePudding user response：
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