with cte as

(

select id,parent_id from [dbo].[manager_Department] where id=42

union all

select b.id,b.parent_id from cte a join [dbo].[manager_Department] b on b.parent_id=a.id

)

select top 1 stuff ( (select ( ','+convert(varchar(50), id)) from cte for xml path('') ) ,1,1,'') as col from cte

从带有层级的部门表中取出当前部门及下级部门的id,并转成行