;
with subDepartment
as ( select
BesonDepartmentID,
DepartmentName,
ParentBesonDepartmentID,
1 as HierarchyLevel
from dbo.[_Department]
where
BesonDepartmentID = 1
union all
select
A.BesonDepartmentID,
A.DepartmentName,
A.ParentBesonDepartmentID,
B.HierarchyLevel + 1 AS HierarchyLevel
from dbo.[_Department] A, subDepartment B
where A.ParentBesonDepartmentID = B.BesonDepartmentID
)
select * from subDepartment
