T-SQL recursive query (a method for a given node to check all parent nodes, all child nodes)

--Find all parent nodes
With TAB as
(
Select Type_id,parentid,type_name from sys_paramtype_v2_0 where type_id=316--child nodes
UNION ALL
Select B.type_id,b.parentid,b.type_name
From
tab a,--child node datasets
Sys_paramtype_v2_0 B--parent node data set
Where a.parentid=b.type_id--the child node DataSet. Parendid= The parent node data set. Id
)
SELECT * from tab;

--Find all child nodes
With TAB as
(
Select Type_id,parentid,type_name from sys_paramtype_v2_0 where type_id=1--parent node
UNION ALL
Select B.type_id,b.parentid,b.type_name
From
tab a,--parent node dataset
SYS_PARAMTYPE_V2_0 b--child node data set
Where b.parentid=a.type_id--the child node DataSet. Id= The parent node data set. Parendid
)
SELECT * from tab;

--Find the path from the child node to the tier node
With TAB as
(
Select Type_id,parentid,type_name,cast (type_id as varchar) as Fulltypeid
From Sku_producttype where type_id=423--child nodes
UNION ALL
Select
B.type_id,b.parentid,b.type_name,
Cast (a.fulltypeid+ ', ' +cast (b.type_id as nvarchar) as varchar) as Fulltypeid
From
tab a,--child node datasets
Sku_producttype B--parent node data set
Where a.parentid=b.type_id--the child node DataSet. Parendid= The parent node data set. Id
)
SELECT * from tab;

--------------Results--------------
423 410 Bee Pollen 423
410 347 Bee Products 423,410
347 5 Nutritious Food 423,410,347
5 0 Health Care 423,410,347,5

Excerpt from: http://write.blog.csdn.net/postedit/7869241

T-SQL recursive query (a method for a given node to check all parent nodes, all child nodes)