SQL recursive query from bottom to top

The task is to build a tree in a table with parentID from bottom to top. It's useful for generating hierarchy strings, lists or any other types. You can also create a storage procedure of this and it will make it easy to use in queries.
So, the example of the code below:
1 2 3 4 5 6 7 8 9 10 11 12 |
DECLARE @childID INT SET @childID = 5 --the bottom id to search ;WITH RTE AS ( SELECT *, 1 AS Lvl FROM Departments WHERE EntityId = @childID UNION ALL SELECT rh.*, Lvl+1 AS Lvl FROM Departments rh INNER JOIN RTE rc ON rh.EntityId = rc.Parent ) SELECT EntityId, Name, Lvl FROM RTE r |