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
精彩评论