sqL 根据parentID childID 隶属关系，拼接到字段

declare m_cursor cursor scroll for
select top 10 DeptID,DeptParentID from  Department
 
open m_cursor
declare   @DeptID varchar(100), @DeptParentID varchar(100),@aaaa varchar(1000)
 
fetch next from m_cursor into @DeptID,@DeptParentID
while @@FETCH_STATUS=0
begin
       
   WITH Tree
   AS
    (
        SELECT DeptID,DeptParentID,DeptName FROM Department C WHERE C.DeptID = @DeptID -- child id
        UNION ALL
        SELECT P.DeptID,P.DeptParentID,p.DeptName FROM Department P
        INNER JOIN Tree T ON P.DeptID = T.DeptParentID
    )
    select @aaaa=(SELECT  CAST(DeptName AS varchar)+ '-'  FROM Tree FOR XML PATH(''))
    print  @aaaa
    fetch next from m_cursor into @DeptID,@DeptParentID
end
 
close m_cursor
deallocate m_cursor

 

declare m_cursor cursor scroll forselect top 10 DeptID,DeptParentID from  Department open m_cursordeclare   @DeptID varchar(100), @DeptParentID varchar(100),@aaaa varchar(1000) fetch next from m_cursor into @DeptID,@DeptParentIDwhile @@FETCH_STATUS=0begin          WITH Tree   AS(SELECT DeptID,DeptParentID,DeptName FROM Department C WHERE C.DeptID = @DeptID -- child idUNION ALLSELECT P.DeptID,P.DeptParentID,p.DeptName FROM Department PINNER JOIN Tree T ON P.DeptID = T.DeptParentID)    select @aaaa=(SELECT  CAST(DeptName AS varchar)+ '-'  FROM Tree FOR XML PATH(''))    print  @aaaa    fetch next from m_cursor into @DeptID,@DeptParentIDend close m_cursordeallocate m_cursor