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

 1 declare m_cursor cursor scroll for
 2 select top 10 DeptID,DeptParentID from  Department
 3  
 4 open m_cursor
 5 declare   @DeptID varchar(100), @DeptParentID varchar(100),@aaaa varchar(1000)
 6  
 7 fetch next from m_cursor into @DeptID,@DeptParentID
 8 while @@FETCH_STATUS=0
 9 begin
10        
11    WITH Tree
12    AS
13     (
14         SELECT DeptID,DeptParentID,DeptName FROM Department C WHERE C.DeptID = @DeptID -- child id
15         UNION ALL
16         SELECT P.DeptID,P.DeptParentID,p.DeptName FROM Department P
17         INNER JOIN Tree T ON P.DeptID = T.DeptParentID
18     )
19     select @aaaa=(SELECT  CAST(DeptName AS varchar)+ '-'  FROM Tree FOR XML PATH(''))
20     print  @aaaa
21     fetch next from m_cursor into @DeptID,@DeptParentID
22 end
23  
24 close m_cursor
25 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

转载于:https://www.cnblogs.com/sharpmap/p/6523976.html