It's a little complicated. I'm confused today.

First, put in the pages you need

https://blog.csdn.net/u011872945/article/details/14452641

This person wrote a function in SQL server. You can find the parent node from the child node or all the child nodes from the parent node. It's powerful, and it uses recursion.

https://zhidao.baidu.com/question/1495468298825553659.html

It's a bit fierce to see the answer below this question by chance. I haven't been familiar with the process of recursion. I'm a bit more complicated. This time I choose to restore CTE step by step. https://zhidao.baidu.com/question/457085519108307205.html

After the restoration, I understood a little, but the God's select part was a bit domineering. I don't understand. I'll come back to study hard some other day and write it down first.

In order to restore the recursive process in CTE, it is just like a fool to build a new table. test1，test2. I also learned about STUFF and FRO XML PATH in SQL Server China.

A discussion on the problem of stitching multiple lines of a column into one line, stuff as well as for xml path Combined use of, excellent.
//Dismantling
--1.
select 1 leave,parent,place,cast('' as varchar(10)) r from hf
leave	parent	place	r
1	0	China	
1	1	Guangdong	
1	1	Anhui	
1	1	Henan	
1	1	Hebei	
1	100	Shenzhen	
1	100	Guangzhou	
1	10001	Shenzhen A area	
1	10001	Shenzhen B area	
--2.
WITH t as (select 1 leave,parent,place,cast('' as varchar(10)) r from hf)
select *
FROM hf c
INNER JOIN t on c.Dept_code=t.parent

Dept_code	parent	place	leave	parent1	Location 1	r
1	0	China	1	1	Guangdong	
1	0	China	1	1	Anhui	
1	0	China	1	1	Henan	
1	0	China	1	1	Hebei	
100	1	Guangdong	1	100	Shenzhen	
100	1	Guangdong	1	100	Guangzhou	
10001	100	Shenzhen	1	10001	Shenzhen A area	
10001	100	Shenzhen	1	10001	Shenzhen B area	

WITH t as (select 1 leave,parent,place,cast('' as varchar(10)) r from hf)
select t.leave+1 leave,c.parent,t.place 'place',c.place r
FROM hf c
INNER JOIN t on c.Dept_code=t.parent

CREATE TEMPORARY TABLE 
leave	parent	place	r
1	0	China	
1	1	Guangdong	
1	1	Anhui	
1	1	Henan	
1	1	Hebei	
1	100	Shenzhen	
1	100	Guangzhou	
1	10001	Shenzhen A area	
1	10001	Shenzhen B area	
2	0	Guangdong	China
2	0	Anhui	China
2	0	Henan	China
2	0	Hebei	China
2	1	Shenzhen	Guangdong
2	1	Guangzhou	Guangdong
2	100	Shenzhen A area	Shenzhen
2	100	Shenzhen B area	Shenzhen

create table test1
(leave varchar(10), 
 parent varchar(10),
 //Location varchar(10), 
 r varchar(10));

SELECT * INTO test1
FROM
(select 1 leave,parent,place,cast('' as varchar(10)) r from hf) aa

WITH t as (select 1 leave,parent,place,cast('' as varchar(10)) r from hf)
INSERT INTO test1 
SELECT * 
FROM
		(select t.leave+1 leave,c.parent,t.place 'place',c.place r
		FROM hf c
		INNER JOIN t on c.Dept_code=t.parent
		) bb


INSERT INTO test2
select test1.leave+1 leave,c.parent,test1.place 'place',c.place r
FROM hf c
INNER JOIN test1 on c.Dept_code=test1.parent

Dept_code	parent	place	leave	parent1	Location 1	r
1	0	China	1	1	Guangdong	
1	0	China	1	1	Anhui	
1	0	China	1	1	Henan	
1	0	China	1	1	Hebei	
100	1	Guangdong	1	100	Shenzhen	
100	1	Guangdong	1	100	Guangzhou	
10001	100	Shenzhen	1	10001	Shenzhen A area	
10001	100	Shenzhen	1	10001	Shenzhen B area	
1	0	China	2	1	Shenzhen	Guangdong
1	0	China	2	1	Guangzhou	Guangdong
100	1	Guangdong	2	100	Shenzhen A area	Shenzhen
100	1	Guangdong	2	100	Shenzhen B area	Shenzhen

test2 The table is CTE Result

select d.place,
 stuff((select '/'+r from test2 e 
        where e.place=d.place
        order by leave desc 
        for xml path('')),1,1,'')+d.place 'paths'
 from test2 d group by d.place

select '/'+r from test2 for xml path('');

select stuff((select '/'+r from test2 for xml path('')),1,1,'')
//There are fewer/