SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dep 
(
	id varchar(4),
	name nvarchar(10),
	upperid varchar(4)
);

insert into dep
values
('1403','A','4005'),
('1406','B','2101'),
('1700','C','2101'),
('1701','D','1700'),
('1705','E','1403'),
('1711','F','3008'),
('1715','G','3008'),
('1716','H','3002'),
('1717','I','3002'),
('1722','J','3002'),
('1724','K','3002'),
('1725','L','7120'),
('1731','M','3398'),
('1732','N','2101'),
('1738','O','3005'),
('1739','P','3005'),
('1740','Q','3398'),
('1741','R','3398'),
('1742','S','3007'),
('1744','T','3007'),
('1745','U','3007'),
('1746','V','3007'),
('1756','W','3007'),
('1758','X','3007'),
('1760','Y','3007'),
('1773','Z','1725'),
('1782','AA','1725'),
('1787','AB','1700'),
('1795','AC','1403'),
('1799','AD','1700'),
('1804','AE','9999'),
('1807','AF','1725'),
('1808','AG','1406'),
('3007','AH','4005'),
('2101','AI','9999'),
('9999','AJ',''),
('3008','AK','2101'),
('3002','AL','2101'),
('7120','AM','3012'),
('3012','AN','9999'),
('3398','AO','3012'),
('3005','AP','2101'),
('3007','AQ','4005'),
('4005','AR','9999');
SELECT *
FROM dep
order by id;


id
name
upperid


1403
A
4005

1406
B
2101

1700
C
2101

1701
D
1700

1705
E
1403

1711
F
3008

1715
G
3008

1716
H
3002

1717
I
3002

1722
J
3002

1724
K
3002

1725
L
7120

1731
M
3398

1732
N
2101

1738
O
3005

1739
P
3005

1740
Q
3398

1741
R
3398

1742
S
3007

1744
T
3007

1745
U
3007

1746
V
3007

1756
W
3007

1758
X
3007

1760
Y
3007

1773
Z
1725

1782
AA
1725

1787
AB
1700

1795
AC
1403

1799
AD
1700

1804
AE
9999

1807
AF
1725

1808
AG
1406

2101
AI
9999

3002
AL
2101

3005
AP
2101

3007
AQ
4005

3007
AH
4005

3008
AK
2101

3012
AN
9999

3398
AO
3012

4005
AR
9999

7120
AM
3012

9999
AJ



select
ISNULL(dep1.id,'') dep1,ISNULL(dep2.id,'') dep2,ISNULL(dep3.id,'') dep3
,ISNULL(dep4.id,'') dep4,ISNULL(dep5.id,'') dep5,ISNULL(dep6.id,'') dep6
,ISNULL(dep1.name,'') depname1,ISNULL(dep2.name,'') depname2,ISNULL(dep3.name,'') depname3
,ISNULL(dep4.name,'') depname4,ISNULL(dep5.name,'') depname5,ISNULL(dep6.name,'') depname6
from dep dep1
left join dep dep2 on dep2.upperid=dep1.id
left join dep dep3 on dep3.upperid=dep2.id
left join dep dep4 on dep4.upperid=dep3.id
left join dep dep5 on dep5.upperid=dep4.id
left join dep dep6 on dep6.upperid=dep5.id
where dep1.id='2101'


dep1
dep2
dep3
dep4
dep5
dep6
depname1
depname2
depname3
depname4
depname5
depname6


2101
1406
1808



AI
B
AG




2101
1700
1701



AI
C
D




2101
1700
1787



AI
C
AB




2101
1700
1799



AI
C
AD




2101
1732




AI
N





2101
3008
1711



AI
AK
F




2101
3008
1715



AI
AK
G




2101
3002
1716



AI
AL
H




2101
3002
1717



AI
AL
I




2101
3002
1722



AI
AL
J




2101
3002
1724



AI
AL
K




2101
3005
1738



AI
AP
O




2101
3005
1739



AI
AP
P





with temp as
(
 select id
 -- ,upperid,name 
 from dep 
 where id='2101' -- 由 2101 往下展
 union all
 select b.id
 -- ,b.upperid,b.name 
 from temp a,dep b
 where b.upperid=a.id 
)
select * from temp
-- 不含 2101
where id<>'2101'
order by id;


id


1406

1700

1701

1711

1715

1716

1717

1722

1724

1732

1738

1739

1787

1799

1808

3002

3005

3008

id	name	upperid
1403	A	4005
1406	B	2101
1700	C	2101
1701	D	1700
1705	E	1403
1711	F	3008
1715	G	3008
1716	H	3002
1717	I	3002
1722	J	3002
1724	K	3002
1725	L	7120
1731	M	3398
1732	N	2101
1738	O	3005
1739	P	3005
1740	Q	3398
1741	R	3398
1742	S	3007
1744	T	3007
1745	U	3007
1746	V	3007
1756	W	3007
1758	X	3007
1760	Y	3007
1773	Z	1725
1782	AA	1725
1787	AB	1700
1795	AC	1403
1799	AD	1700
1804	AE	9999
1807	AF	1725
1808	AG	1406
2101	AI	9999
3002	AL	2101
3005	AP	2101
3007	AQ	4005
3007	AH	4005
3008	AK	2101
3012	AN	9999
3398	AO	3012
4005	AR	9999
7120	AM	3012
9999	AJ