SQL Server 2017

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

CREATE TABLE Dep
	([Dept_ID]  varchar(4), [PR_Dept_ID] varchar(4), [DeptName] varchar(5))
;
	
INSERT INTO Dep
	([Dept_ID], [PR_Dept_ID], [DeptName])
VALUES
	('0000', NULL, 'HR00'),
	('0001', '0000', 'HR01'),
	('0002', '0000', 'HR02'),
	('0003', '0002', 'HR03'),
	('0004', '0002', 'HR04'),
	('0005', '0004', 'HR05'),
	('0006', NULL, 'INV00'),
	('0007', '0006', 'INV01'),
	('0008', '0007', 'INV02')
;
9 rows affected

select * from Dep


Dept_ID
PR_Dept_ID
DeptName


0000
null
HR00

0001
0000
HR01

0002
0000
HR02

0003
0002
HR03

0004
0002
HR04

0005
0004
HR05

0006
null
INV00

0007
0006
INV01

0008
0007
INV02


;with cte as (
	select *,cast(Dept_ID as varchar(255)) top_dept_id from dep where pr_dept_id is null
	union all
	select t1.*,t2.top_dept_id 
	from dep t1
	inner join cte t2 on t2.dept_id = t1.pr_dept_id 
)
select  
	Dept_ID,PR_Dept_ID,DeptName
	,case when top_dept_id = cast(Dept_ID as varchar(255)) then null else top_dept_id end as top_dept_id
from cte
order by dept_id


Dept_ID
PR_Dept_ID
DeptName
top_dept_id


0000
null
HR00
null

0001
0000
HR01
0000

0002
0000
HR02
0000

0003
0002
HR03
0000

0004
0002
HR04
0000

0005
0004
HR05
0000

0006
null
INV00
null

0007
0006
INV01
0006

0008
0007
INV02
0006

Dept_ID	PR_Dept_ID	DeptName
0000	null	HR00
0001	0000	HR01
0002	0000	HR02
0003	0002	HR03
0004	0002	HR04
0005	0004	HR05
0006	null	INV00
0007	0006	INV01
0008	0007	INV02