clear markdown compare help donate comments/suggestions/bugs a leap of faith? diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith?
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 432217 distinct fiddles created so far.

CREATE TABLE #table ( id INT identity(1, 1) ,[Name] VARCHAR(16) ,ParentID INT NULL ) INSERT INTO #table VALUES('John',null) ,('Doe',1) ,('Mike',1) ,('Neil',3) ,('Sylvia',4) ,('Mary',2) ;WITH CTE AS ( SELECT id ,ParentID,1 DirectP FROM #table UNION ALL SELECT r.ID ,t.parentID,0 DirectP FROM #table t INNER JOIN CTE r ON t.id = r.parentID WHERE t.ParentID IS NOT NULL ) SELECT r.ID ,r.ParentID ,DirectP FROM CTE r ORDER BY id ,ParentID DESC
ID ParentID DirectP
1 1
2 1 1
3 1 1
4 3 1
4 1 0
5 4 1
5 3 0
5 1 0
6 2 1
6 1 0
 hidden batch(es)