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:

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

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

26 June 2020, admin
SQL
Tags: MSSQL, SQL query, Transact-SQL
Comments: (0)

SQL recursive query from bottom to top

No Comments »

Leave a comment

New posts