Prepare N Level Recursive/ Hierarchical List Using Common Table Expressions

Display Result In Hierarchical Level  In SQL ?
Prepare Hierarchy as Per Parent - Child Relationship ?


If you want to build recursive query for n level depth then you should go with CTE (Common Table Expressions).

Here i explain Simple Example so you will get better idea.


Declare @Temp Table
(
ID INT IDENTITY(1,1),
Name Varchar(50),
ParentID INT NULL
)

Declare @Depth INT

Set @Depth = 3; -- According No of Max Childs into any Parent ex 3 Means it Works for (Max 10^3 - 1) Childs

--Select * From @Temp

INSERT INTO @Temp
Values    ('HItesh Patel',0),
        ('Ajay Patel',1),
        ('Matang Dave',2),
        ('Tejas Patel',3),
        ('Vishal Patel',4),
        ('Jignesh Patel',7),
        ('Rajesh Patel',1),
        ('Mahesh Patel',3),
        ('Amit Patel',0),
        ('Piyush Patel',1),
        ('Ritesh Patel',1)
 
Select * From @Temp;


WITH List (    ID, Name, IndexNo, lvl) AS
(
    SELECT        i.ID, i.Name,
                CONVERT(FLOAT,ROW_NUMBER() OVER (ORDER BY i.Name)) IndexNo,
                1 lvl
    FROM        @Temp i
    WHERE       ISNULL(ParentID, 0)  =  0  

    UNION ALL

    SELECT        i.ID, i.Name,
                CONVERT(FLOAT, List.IndexNo + (CONVERT(FLOAT,ROW_NUMBER() OVER (ORDER BY i.Name)) * POWER(CONVERT(FLOAT,10) ,-1 * @Depth * List.lvl))) IndexNo,
                List.lvl + 1 lvl
    FROM        @Temp i
    INNER JOIN    List   ON  i.ParentID = List.ID
)

SELECT        ID, Name, REPLICATE('       ',lvl - 1) + Name
FROM        List
ORDER BY    IndexNo


Here @temp Table has Columns ID,Name, ParentID.
ID - Primary Key
Name - Name of Employee
ParentID - ID of Person which is Parent of him/her, which may be null or 0 as per your requirement.

Notes : 
ParentID  -> Null or 0 means it will be Top Most Parent  and any n > 0 value indicate that
Depth       -> Set value of @Depth according to Possibility of No of Maximum Child of any Parent
for ex if you set Depth = 3 then query works for only 999 (10^3 -1) child of any parents
means set 4 if possibility is 9999 child .....