WITH Dep_CTE (inID,deptID,DEPNa,DEP_PATH,DEP_LEVEL) AS
(
--頂層
select inID,deptID,[name],CAST([name] as varchar(MAX)),0 AS DT_LEVEL
from Dept
where inID IN ('00000M')
union all
--成員
select D.inID,D.deptID,D.[name],CAST(DC.DEP_PATH+' > '+D.name as varchar(MAX)),DC.DEP_LEVEL+1
from Dept D INNER JOIN Dep_CTE DC on D.inID=DC.deptID
)
select * from Dep_CTE ;