SQL statement recursive query with as to find all child nodes

Tag: Option NIO Manager SQL statement from Count STR char node

CREATE TABLE #EnterPrise
(
Department nvarchar (,--) department name
Parentdept nvarchar (,--) Superior Department
Departmanage nvarchar ()--Department manager
)

insert into  #EnterPrise  select  ' technology ', ' general manager ', ' Tom '
Insert into   #EnterPrise  select  ' mofcom ', ' General manager ', ' Jeffry '
insert into  #EnterPrise  select  ' Business unit ', ' mofcom ', ' ViVi '
insert into  #EnterPrise  select  ' Business Two ', ' Ministry of Commerce ', ' Peter '
insert  into  #EnterPrise  select  ' program group ', ' technology department ', ' GiGi '
insert into  #EnterPrise  select   ' Design group ', ' technology department ', ' Yoyo '
insert into  #EnterPrise  select  ' special group ', ' program group ', ' Yue '
Insert  into  #EnterPrise  select  ' general manager ', ' ', ' Boss '

--Query department manager is Tom's following department name
; with HgO as
(
SELECT *,0 as rank from #EnterPrise where departmanage= ' Tom '
UNION ALL
Select h.*,h1.rank+1 from #EnterPrise H joins HgO H1 on H.parentdept=h1. Department
)
SELECT * FROM HgO

/*
Department parentdept departmanage Rank
--------------- -------------------- ----------------------- -----------
Technical Department general manager Tom 0
Program Group Technical Department GiGi 1
Design Group Technical Department Yoyo 1
Special Group program group Yue 2
*/

--Query department manager is Gigi's superior department name
; with HgO as
(
SELECT *,0 as rank from #EnterPrise where departmanage= ' GiGi '
UNION ALL
Select h.*,h1.rank+1 from #EnterPrise H joins HgO H1 on H.department=h1. Parentdept
)
SELECT * FROM HgO

/*
Department parentdept departmanage Rank
-------------------- ----------------------  -----------  -----------
Program Group Technical Department GiGi 0
Technical Department general manager Tom 1
General Manager Boss 2
*/

If the recursion count is greater than 100, simply add option (maxrecursion 0) to the SQL statement connected to the CTE. Default recursion

The number of times is 100, and setting to 0 indicates no limit.

SQL statement recursive query with as to find all child nodes