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:

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