Hierarchical Query example

A Simple demonstration of Hierarchical queries in SQL Server with the help of recursion using Common Table Expression

Create Table 

CREATE TABLE table_relation_1(
    Id INT NOT NULL,
    Name VARCHAR(100) NOT NULL,
    ParentId INT NULL   
)

Insert Rows into the Table

ALTER PROCEDURE insert_data
AS
BEGIN
    DECLARE
    @maxcount int=10,
    @count int=1,
    @parentId int

    WHILE @count<@maxcount
    BEGIN
    IF @count=1
    set @parentId=null;
    ELSE
    set @parentId=@count-1;
   
    INSERT INTO table_relation_1 VALUES(@count,convert(varchar(20),@count)+'_USER',@parentId);
    SET @count=@count+1;
    END
END

Run a recursive query with the help of CTE to get the Parent Child relationship  with generation.

;with CTE(ChildId, ChildName,generation,parentId)
AS
(
  select id,name,0,ParentId
  from table_relation_1
  where ParentId is null

  union all

  select id,name ,generation+1,ChildId
  from table_relation_1 nxg
  inner join CTE on nxg.ParentId=CTE.ChildId
)

select * from CTE

Run a recursive query with the help of CTE to get the parent child relationship from a certain ID

;with CTE
AS
(
select * from table_relation_1 where id=5
union all

select a.* from table_relation_1 a inner join CTE b on a.Id=b.ParentId

)

select * from CTE