SQL tree Solution

Create Table Tb (ID varchar (3), PID varchar (3), name varchar (10) insert into TB values ('001', null, 'guangdong province ') insert into TB values ('002 ', '001', 'guangzhou ') insert into TB values ('003', '001', 'shenzhen ') insert into TB values ('004 ', '002', 'tianhe district ') insert into TB values ('005', '003 ', 'luohu district ') insert into TB values ('006 ', '003', 'futian district ') insert into TB values ('007', '003 ', 'baoan district ') insert into TB values ('008 ', '007', 'West township ') insert into TB values ('009', '007', 'longhua Zhen ') insert into TB values ('010 ', '007', 'songgang Zhen ') Go -- query the parent path function of each node (from parent to child) create Function f_pid1 (@ ID varchar (3) returns varchar (100) asbegin declare @ re_str as varchar (100) set @ re_str = ''select @ re_str = Name from TB where id = @ ID while exists (select 1 from TB where id = @ ID and PID is not null) begin select @ ID = B. ID, @ re_str = B. name + ',' + @ re_str from tb a, tb B where. id = @ ID and. PID = B. ID end return @ re_strendgo -- query the parent path function of each node (from child to parent) Create Function f_pid2 (@ ID varchar (3) returns varchar (100) asbegin declare @ re_str as varchar (100) set @ re_str = ''select @ re_str = Name from TB where id = @ ID while exists (select 1 from TB where id = @ ID and PID is not null) begin select @ ID = B. ID, @ re_str = @ re_str + ',' + B. name from tb a, tb B where. id = @ ID and. PID = B. ID end return @ re_strendgoselect *, DBO. f_pid1 (ID) [path (from parent to child)], DBO. f_pid2 (ID) [path (from child to parent)] from TB order by iddrop function f_pid1, f_pid2drop table TB/* id pid name path (from parent to child) path (from child to parent) ---- ------ ----------------------------- ------------------------ 001 null Guangdong Province 002 001 Guangzhou City GuangDong Province Guangzhou City, Guangzhou City, Guangdong Province 003 001 Shenzhen Guangdong Province, Shenzhen City, Guangdong Province 004 002 Tianhe District, Guangzhou City, Tianhe District, Guangzhou City, 005 003 Luohu District, Guangdong Province, Shenzhen City, Luohu District, Shenzhen City, 006 003 Futian district, Guangdong Province, Shenzhen City, Futian district, Shenzhen City, Guangdong Province, 007 003 Baoan District, Guangdong Province, Shenzhen City, Baoan District, Shenzhen City, guangdong province 008 007 West township Guangdong Province, Shenzhen City, Baoan District, Xi township West township, Baoan District, Shenzhen City, Guangdong Province 007 Longhua town Guangdong Province, Shenzhen City, Baoan District, Longhua town, Baoan District, Shenzhen City, songgang town, 010 007, Guangdong Province, Shenzhen City, Baoan District, songgang town, Baoan District, Shenzhen City, and Guangdong Province (10 rows affected )*/

/* Title: How to query a specified node and all its parent nodes in SQL Server 2005 (displayed in string form) Author: aixinjue Luo Xiaohua (the wind and rain in the past 18 years, the snow blossoms in the snow) Time: location: Urumqi, Xinjiang */create table Tb (ID varchar (3), PID varchar (3), name nvarchar (10) insert into TB values ('001', null, N 'guangdong province ') insert into TB values ('002', '001', N 'guangzhou City ') insert into TB values ('003', '001 ', N 'shenzhen City ') insert into TB values ('004', '002 ', N 'tianhe district') insert into TB values ('005 ', '003 ', N 'luohu district ') insert into TB values ('006', '003 ', N 'futian district') insert into TB values ('007 ', '003 ', N 'baoan district ') insert into TB values ('008', '007 ', N 'West township') insert into TB values ('009', '007 ', N 'longhua Zhen ') insert into TB values ('010', '007 ', n' songgang Zhen') Go; with T as (select ID, PID = ID from TB Union all select T. ID, pid = TB. PID from t inner join TB on T. PID = TB. ID) Select ID, [path (from parent to child)] = stuff (select ',' + PID from t where id = Tb. id order by T. ID, T. PID for XML Path (''), 1, 1,''), [path (from child to parent)] = stuff (select ', '+ PID from t where id = Tb. ID for XML Path (''), 1, 1,'') from tbgroup by idorder by ID/* ID path (from parent to child) path (from child to parent) ---- --------------- 001 001002 001,002 001,003 001,002,004 004,002,001 001,003,005 005,003,001 001,003,006 006,003,001 001,003,007 007,003,001 001,003,007,008 008,007,003,001 001,003,007,009 009,007,003,001 001,003,007,010 010,007,003,001 (10 rows affected )*/; with T as (select ID, name, pid = ID, Path = cast (name as nvarchar (100) from TB Union all select T. ID, T. name, pid = TB. PID, Path = cast (TB. name as nvarchar (100) from t join TB on TB. id = T. PID) Select ID, name, [path (from parent to child) _ 1] = pid1, [path (from parent to child) _ 2] = reverse (substring (reverse (path1 ), charindex (',', reverse (path1) + 1, Len (path1), [path (from child to parent) _ 1] = pid2, [path (from child to parent) _ 2] = substring (path2, charindex (',', path2) + 1, Len (path2) from (select ID, name, pid1 = stuff (select ',' + PID from t where id = Tb. id order by T. ID, T. PID for XML Path (''), 1, 1,''), pid2 = stuff (select ',' + PID from t where id = Tb. ID for XML Path (''), 1, 1,''), path1 = stuff (select ',' + path from t where id = Tb. id order by T. ID, T. PID for XML Path (''), 1, 1,''), path2 = stuff (select ',' + path from t where id = Tb. ID for XML Path (''), 1, 1,'') from tbgroup by ID, name) morder by ID/* ID name path (from parent to child) _ 1 path (from parent to child) _ 2 path (from child to parent) _ 1 path (from child to parent) _ 2 ---- ------ ---------------- ------------------ ----------------------------- 001 Guangdong 001 Guangdong 001 Guangdong 002 Guangzhou 001,002 Guangdong, Guangzhou 002,001 Guangzhou, Guangdong 003 Shenzhen 001,003 Guangdong, Shenzhen 003,001 Shenzhen, Guangdong 004 Tianhe District 001,002,004 Guangdong, guangzhou, Tianhe District, 004,002,001 Tianhe District, Guangzhou City, 005 Luohu District, Guangdong Province, 001,003,005 Guangdong Province, Shenzhen City, Luohu District, 005,003,001 Luohu District, Shenzhen City, 006 Futian district, Guangdong Province, 001,003,006 Guangdong Province, Shenzhen City, Futian district, 006,003,001 Futian district, Shenzhen City, baoan District, Guangdong Province, 001,003,007 Baoan District, Shenzhen City, Baoan District, 007,003,001 Baoan District, Shenzhen City, 008 Township, Guangdong Province, 001,003,007,008 Guangdong Province, Baoan District, Baoan District, Shenzhen City, 008,007,003,001 West township, Baoan District, Shenzhen City, longhua town, 001,003,007,009 Guangdong Province, 009,007,003,001 Shenzhen City, Baoan District, Longhua town, 001,003,007,010 Longhua town, Baoan District, Shenzhen City, 010 songgang town, Guangdong Province, 010,007,003,001 Guangdong Province, Shenzhen City, Baoan District, songgang town, songgang town, Baoan District, Shenzhen City, guangdong (10 rows affected) */drop table TB