Find the Parents and Adjacent Members of the Node using T-SQL

This is one of the challenges taken from BeyondRelational.

Below is the problem statement.

The challenge is to identify all 'direct' or 'indirect' parents of a given child node and all its siblings.

Sample Input Data

ParentId ChildId    Name

-------- ------- --------

NULL 1 Niladri Biswas

1 2 Piyush Ghosh

1 3 Agnish Basu

1 4 Deepak Kumar Goyal

2 5 Sachin Srivastav

2 6 Nishant Mandilwar

5 7 Arindam Pal

5 8 Mahi Sharma

3 9 Mahima Roy

3 10 Simran Motilal

9 11 Raj Malhotra

9 12 Sharmistha Roy

10 13 Preeti Sen

10 14 Holly Huggins

Given a @ChildId = 6 as Parameter should give below output

Expected Output

Id Name

--- ----

1 Niladri Biswas

3    Agnish Basu

4    Deepak Kumar Goyal

2    Piyush Ghosh

6        Nishant Mandilwar

5        Sachin Srivastav

Script to generate the data

DECLARE @ParentChild TABLE(Parentid INT,Childid INT,Name Varchar(20) );

INSERT INTO @ParentChild 

SELECT null, 1, 'Niladri Biswas' UNION ALL 

SELECT 1, 2 ,'Piyush Ghosh' UNION ALL  

SELECT 1,3 ,'Agnish Basu' UNION ALL  

SELECT 1,4,'Deepak Kumar Goyal'  UNION ALL

SELECT 2,5, 'Sachin Srivastav' UNION ALL 

SELECT 2,6, 'Nishant Mandilwar' UNION ALL 

SELECT 5,7 ,'Arindam Pal' UNION ALL 

SELECT 5,8,'Mahi Sharma' UNION ALL 

SELECT 3,9, 'Mahima Roy' UNION ALL 

SELECT 3,10, 'Simran Motilal' UNION ALL 

SELECT 9,11,'Raj Malhotra' UNION ALL

SELECT 9,12,'Sharmistha Roy' UNION ALL 

SELECT 10,13,'Preeti Sen'  UNION ALL 

SELECT 10,14,'Holly Huggins'

Also, below are the other Restrictions or notes that should be considered.

The solution should work on SQL Server 2005 and above.

Column names should respect the desired output shown

Output must be sorted in DESCENDING ORDER of Name

The output should show the hierarchical relationship between the employees (as shown in the example above)

The program has to be done by a single query and should begin either with a SELECT or WITH statement with no variables, temporary table, table variables permitted.

You cannot use RBAR, cursors, loops etc. in your program. 

Solution

DECLARE @ParentChild TABLE(Parentid INT,Childid INT,Name Varchar(20) );

INSERT INTO @ParentChild

      SELECT null, 1, 'Niladri Biswas' UNION ALL

      SELECT 1, 2 ,'Piyush Ghosh' UNION ALL 

      SELECT 1,3 ,'Agnish Basu' UNION ALL 

      SELECT 1,4,'Deepak Kumar Goyal'  UNION ALL

      SELECT 2,5, 'Sachin Srivastav' UNION ALL

      SELECT 2,6, 'Nishant Mandilwar' UNION ALL

      SELECT 5,7 ,'Arindam Pal' UNION ALL

      SELECT 5,8,'Mahi Sharma' UNION ALL

      SELECT 3,9, 'Mahima Roy' UNION ALL

      SELECT 3,10, 'Simran Motilal' UNION ALL

      SELECT 9,11,'Raj Malhotra' UNION ALL

      SELECT 9,12,'Sharmistha Roy' UNION ALL

      SELECT 10,13,'Preeti Sen'  UNION ALL

      SELECT 10,14,'Holly Huggins'

DECLARE @ChildId INT

SELECT @ChildId = 6    

     

;WITH WHOLE_TREE

AS

(

      SELECT Childid, Parentid, Name, 0 as level

      FROM @ParentChild

      where Parentid is null

     

      UNION ALL

     

      SELECT pc.Childid, pc.Parentid, pc.Name, (d.level + 1) as level

      FROM WHOLE_TREE d

            INNER JOIN @ParentChild pc

                  ON d.Childid = pc.Parentid

),

SUBTREE

AS

(

      SELECT w.Childid, w.Parentid, w.Name, w.level FROM WHOLE_TREE w

      WHERE          level < (SELECT level FROM WHOLE_TREE WHERE Childid = @ChildId)

     

      UNION

     

      SELECT w.Childid, w.Parentid, w.Name, w.level FROM WHOLE_TREE w

      WHERE          level = (SELECT level FROM WHOLE_TREE w2 WHERE Childid = @ChildId)

              AND w.parentId = (SELECT ParentId FROM WHOLE_TREE w3 WHERE ChildId = @ChildId)                             

),

FINAL_OUTPUT

AS

(

      SELECT Childid, CONVERT(VARCHAR,Name) AS Name, level, 0 as processedLevel

      FROM SUBTREE

     

      UNION ALL

     

      SELECT f.Childid, CONVERT(VARCHAR,'     '+f.Name) as Name, f.level, (f.processedLevel + 1) as processedLevel

      FROM FINAL_OUTPUT f

            INNER JOIN SUBTREE s

                  on (f.processedLevel + 1) = S.level

      WHERE (f.processedLevel + 1) <= f.level              

),

DISTINCT_SET

AS

(

      SELECT DISTINCT * FROM FINAL_OUTPUT WHERE level = processedLevel

),

FINAL

AS

(

      SELECT Childid, Name, ROW_NUMBER()OVER(ORDER BY level,Name) as rank FROM DISTINCT_SET

      WHERE level = processedLevel

)

SELECT Childid as Id, Name FROM FINAL

You can find other solutions for the same problem at BeyondRelational