WITH Employee_Tree (ID,
Name,
Manager,
Depth) AS(
SELECT ID,
Name,
Manager,
1 AS depth
FROM Employee WITH (NOLOCK)
WHERE MANAGER IS NULL
UNION ALL
SELECT N.ID,
N.Name,
N.Manager,
ET.Depth + 1
FROM Employee AS N WITH (NOLOCK)
INNER JOIN Employee_Tree ET
ON ET.ID = N.Manager)
SELECT ID,
Name,
Manager,
FROM Employee_Tree
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
From novice to tech pro — start learning today.
ID Parent
1 null
2 1
3 1
4 null
5 4
6 5
with CTE(ID, PARENT)
AS
(
SELECT F.ID, F.PARENT
FROM TABLE1 F
WHERE PARENT IS NULL
UNION ALL
SELECT F.ID, F.PARENT
FROM TABLE1 F
INNER JOIN CTE FH ON FH.ID = F.PARENT
)
SELECT ID, PARENT FROM CTE