t-sql : get a single row with parents, children, grandchildren... n children

I have a table with 2 columns: ID, ParentID

i want to make a new table/view that each row includes all the children in all levels to a certain parents...

for example:

ParentID  Level_1  Level_2  Level_3 ... Level_n

it means that parentI开发者_如何学GoD is the parent of Level_1 which is the Parent of level 2 which is the parent of level_3 and so on...

i need to do it with no recursion so no chance to stack overflow...

in T-SQL there's a function called @@rowcount which returns the rows i got in the last select so maybe i can use it inside a while loop or something...

the question is how can i know how many columns to construct at the beginning ? any idea how to access this challenge ?

---

What you're trying to get is a pivot table, but, in my opinion, this task doesn't belong to the database. I'd rather get a result set with rows and pivot them "externally".

To achieve what I described, in SQL Server 2005+ you can use a Common Table Expression (here you can find an example, while SQL Server 2000 requires a slightly different approach.

Note: although you wrote "no recursion so no chance to stack overflow", you'll still have to proect yourself from infinite loops. I personally use recursion and establish a maximum "depth" for it; when the recursion attempts to go "deeper" than the limit, I stop it and return an error.

---

It can be solved by creating a Table Valued Function TVF, that returns the whole tree of an employee with his level as follows:

CREATE FUNCTION [dbo].[GetTree](@EmployeeID int)
RETURNS @Result Table
(   EmployeeID  int,
    IdParent int,
    Lvl int
)
AS
BEGIN
    declare @lvl int
    set @lvl=0
    while(@EmployeeID is not null)
    begin
        insert  into @Result
        select  EmployeeID,ManagerID,@lvl
        from    dbo.MyEmployees
        where   EmployeeID=@EmployeeID

        select  @EmployeeID=ManagerID,
                @lvl=@lvl+1
        from    dbo.MyEmployees
        where   EmployeeID=@EmployeeID
    end

    update @Result
    set Lvl = (select MAX(Lvl) from @Result)-Lvl+1
RETURN
END

And then simply apply the PIVOT function, to get your output:

SELECT [1] AS Lvl1, [2] AS Lvl2, [3] AS Lvl3, [4] AS Lvl4
FROM 
(select a.EmployeeID,b.EmployeeID EID,b.Lvl
from    dbo.MyEmployees a cross apply
        dbo.GetTree(a.EmployeeID) b) p
PIVOT
(MIN (EID)
FOR Lvl IN
( [1], [2], [3], [4] )
) AS pvt