SQL Server uses CTE recursive queries

• 1. Data Environment Preparation

Refer to the Oracle recursive query article.

• 2. Querying all child nodes under a node

With CTE (ID,NAME,PARENT_ID) as  (    Selectid,name,parent_id fromSc_districtwhereName='Bazhong'Union AllSelectsd.id,sd.name,sd.parent_id fromSc_district SD, CTE CwhereC.id =sd.parent_id)Select* fromcteresult:id name parent_id2Bazhong City14Bazhou District25Tongjiang County26Pingchang County2 -Da Zhai Xiang6 -Rang Tan Zhen6 theLonggang Town6 -White Town6

• 3. Compute hierarchy (level pseudo-column similar to Oracle)

With CTE (Id,name,parent_id,lev) as(   SelectID,NAME,PARENT_ID,1  fromSc_districtwhereName='Dazhou'Union AllSelectsd.id,sd.name,sd.parent_id,c.lev+1  fromSc_district sd,cte CwhereC.id=sd.parent_id)Select* fromcteresult:id name parent_id Lev3Dazhou1    17Tongchuan District3    28Xuanhan County3    29Tahe Xiang8    3TenSanhe Township8    3 OneHu Jia Zhen8    3 ANan Ba Town8    3

• 4. Query the root node of the path (similar to Oracle's Connect_by_root)

With CTE (Id,name,parent_id,rootid,rootname) as(   SelectId,name,parent_id,id Rootid,name Rootname fromSc_districtwhereName='Dazhou'Union AllSelectSd.id,sd.name,sd.parent_id,cte.rootid,cte.rootname fromSc_district sd,ctewhereSd.parent_id=cte.id)Select* fromcteresult:id name parent_id rootid rootname3Dazhou1    3Dazhou7Tongchuan District3    3Dazhou8Xuanhan County3    3Dazhou9Tahe Xiang8    3DazhouTenSanhe Township8    3Dazhou OneHu Jia Zhen8    3Dazhou ANan Ba Town8    3Dazhou

• 5. Querying a recursive path (similar to Oracle's Sys_connect_by_path)

With CTE (Id,name,pathname) as(   SelectId,name,cast (name asnvarchar fromSc_districtwhereName='Dazhou'Union AllSelectSd.id,sd.name,cast (cte.pathname+'_'+sd.name asnvarchar fromSc_district sd,ctewhereSd.parent_id=cte.id)Select* fromCtewith CTE (id,name,pathname) as(   SelectId,name,convert (Nvarchar,name) fromSc_districtwhereName='Dazhou'Union AllSelectSd.id,sd.name,convert (nvarchar,cte.pathname+'_'+sd.name) fromSc_district sd,ctewhereSd.parent_id=cte.id)Select* fromcteresult:id Name Pathname3Dazhou Dazhou7Tongchuan District Dazhou _ Tongchuan District8Xuanhan County Dazhou _ Xuanhan County9Tahe Xiang Dazhou _ Xuanhan County _ Tahe XiangTenSanhe Xiang Dazhou _ Xuanhan County _ Sanhe Xiang OneHu Jia Zhen Dazhou _ Xuanhan County _ hu Jia Zhen ANan Ba Zhen Dazhou _ Xuanhan County _ nan Ba Zhen

SQL Server uses CTE recursive queries