SQL Query method that specifies the node and all its parent nodes

--Query id = All parent nodes of ' 009 'SET @ID = '009'; withT as(  SELECTID, PID, NAME fromTBWHEREId= @ID  UNION  All  SELECTa.id, A.pid, A.name fromTB asAJOINT asB ona.ID=b.pid)SELECT *  fromTORDER  byID/*ID PID NAME------------------001 NULL Guangdong Province 003 001 Shenzhen 007 003 Baoan District 009 007 Longhua Town

Create TableTB (IDvarchar(3), PIDvarchar(3), namevarchar(Ten))Insert  intoTbValues('001',NULL,'Guangdong province')Insert  intoTbValues('002','001','Guangzhou')Insert  intoTbValues('003','001','Shenzhen')Insert  intoTbValues('004','002','Tianhe District')Insert  intoTbValues('005','003','Luohu District')Insert  intoTbValues('006','003','Futian District')Insert  intoTbValues('007','003','Baoan District')Insert  intoTbValues('008','007','West Township')Insert  intoTbValues('009','007','Longhua Town')Insert  intoTbValues('010','007','Songgang Town')Go --querying parent path functions for each node (from parent to child)Create functionF_PID1 (@id varchar(3))returns varchar( -) asbegin  Declare @re_str  as varchar( -)  Set @re_str = "'  Select @re_str =Name fromTbwhereId= @id   while exists(Select 1  fromTbwhereId= @id  andPid is  not NULL)    begin      Select @id =b.ID,@re_str =B.name+ ',' + @re_str  fromTB A, TB Bwherea.ID= @id  andA.pid=b.idEnd  return @re_strEndGo--Querying parent path functions (from child to parent) for each nodeCreate functionF_pid2 (@id varchar(3))returns varchar( -) asbegin  Declare @re_str  as varchar( -)  Set @re_str = "'  Select @re_str =Name fromTbwhereId= @id   while exists(Select 1  fromTbwhereId= @id  andPid is  not NULL)    begin      Select @id =b.ID,@re_str = @re_str + ',' +B.name fromTB A, TB Bwherea.ID= @id  andA.pid=b.idEnd  return @re_strEndGo Select *, DBO.F_PID1 (ID)[path (from parent to child)], Dbo.f_pid2 (ID)[path (from child to parent)] fromTbOrder  byIDDrop functionF_pid1, F_pid2Drop TableTB/*ID PID name path (from parent to Child) path (from child to Parent)-------------------------------------------------   --------------------001 NULL Guangdong province Guangdong Province 002 001 Guangzhou Guangdong Province, Guangzhou Guangzhou, Guangdong Province 003 001 Shenzhen City Guangdong Province, Shenzhen Shenzhen, Guangdong Province 004 002 Tianhe District Guangdong Province, Guangzhou, Tianhe District Tianhe District, Guangzhou, Guangdong Province 005 003 Luohu District, Shenzhen, Luohu District Luohu District, Shenzhen, Guang Eastern Province 006 003 Futian District Guangdong Province, Shenzhen, Futian District Futian District, Shenzhen, Guangdong Province 007 003 Baoan District Guangdong Province, Shenzhen, Baoan District Baoan District, Shenzhen, Guangdong province 008 007 West Township Guangdong province, Shenzhen, Bao ' an district, west  Township West Township, Bao ' An district, Shenzhen, Guangdong Province 009 007 Longhua Town Guangdong Province, Shenzhen, Bao ' An district, Longhua Town Longhua Town, Bao ' An district, Shenzhen, Guangdong province 010 007 Songgang Town of Guangdong Province, Shenzhen, Bao ' An district, Songgang town, Baoan District, Shenzhen, Guangdong Province (the number of rows affected is 10 lines)*/

SQL Query method that specifies the node and all its parent nodes