How to create a recursive CTE to get hierarchical data from a table.
Issue
You are probably reading this article because you need to create a report that displays data from a table which contains rows that have parent-child relationships. Luckily, a CTE can perform recursion up to 100 levels deep.
Solution
Let’s begin by creating a table of hierarchical data. The table has two columns, ID and ParentID. ID is the identifier of that row and ParentID is the identifier of it’s parent row. The two root rows have IDs 5 and 10.
Notice there are two selects within the CTE, the first is the base case and selects the roots. These are easy to identify since they have no ParentID.
The second select is the recursive case which recursively calls HierarchyCTE via the JOIN to get all children of the current ParentID. This recursive call continues until no results are returned or the recursion is 100 levels deep.
The output below shows different details of the hierarchical data.
-- Create a table of hierarchal data.
DECLARE @Data TABLE
(
ID INT NOT NULL
,ParentID INT NULL
)
-- Data contains two root nodes 5 and 10.
-- The paths from root node to leaf nodes are.
-- 5 « 4 « 3 « 2 « 1
-- 5 « 7 « 6
-- 10 « 9 « 8
INSERT INTO @Data (ID, ParentID) VALUES (1,2)
INSERT INTO @Data (ID, ParentID) VALUES (2,3)
INSERT INTO @Data (ID, ParentID) VALUES (3,4)
INSERT INTO @Data (ID, ParentID) VALUES (4,5)
INSERT INTO @Data (ID, ParentID) VALUES (5,NULL)
INSERT INTO @Data (ID, ParentID) VALUES (6,7)
INSERT INTO @Data (ID, ParentID) VALUES (7,5)
INSERT INTO @Data (ID, ParentID) VALUES (8,9)
INSERT INTO @Data (ID, ParentID) VALUES (9,10)
INSERT INTO @Data (ID, ParentID) VALUES (10,NULL)
-- Query the hierarchy.
;WITH HierarchyCTE
AS (
-- Base Case: Get the root nodes.
SELECT --
ID
,ParentID
,[Path] = CAST(ID AS VARCHAR(MAX))
,[Depth] = 0
,[Leaf] = ID
,[Root] = ID
FROM @Data
WHERE ParentID IS NULL
UNION ALL
-- Recursive Case: Recursively call HierarchyCTE via the JOIN to get all children of the ParentID.
SELECT --
dat.ID
,dat.ParentID
,[Path] = CAST(dat.ID AS VARCHAR(MAX)) + '»' + cte.Path
,[Depth] = cte.Depth + 1
,[Leaf] = dat.ID
,[Root] = cte.Root
FROM @DATA dat
INNER JOIN HierarchyCTE cte ON cte.ID = dat.ParentID
)
-- Store the CTE into a temp table so we can query it more than once below.
SELECT *
INTO #Hierarchy
FROM HierarchyCTE cte
ORDER BY cte.ID
,cte.Depth
-- Output 1: Show all relationships.
SELECT --
cte.ID
,cte.ParentID
,cte.Path
,cte.Depth
,cte.Leaf
,cte.Root
FROM #Hierarchy cte
ORDER BY cte.ID
,cte.Depth
-- Output 2: Show only leaf nodes.
-- Filter out all the intermediate relationships so only the leaf-root relationships remain.
SELECT --
cte.Leaf
,cte.Root
,cte.Path
,cte.Depth
FROM #Hierarchy cte
WHERE cte.ID NOT IN (
SELECT ParentID
FROM #Hierarchy
WHERE ParentID IS NOT NULL
)
ORDER BY Leaf
-- Output 3: Show only root nodes.
SELECT --
cte.ID
,cte.ParentID
,cte.Path
,cte.Depth
,cte.Leaf
,cte.Root
FROM #Hierarchy cte
WHERE cte.Leaf = cte.Root
-- Drop TEMP table if it exists.
IF OBJECT_ID('tempdb..#Hierarchy') IS NOT NULL
DROP TABLE #Hierarchy
GO
Output 1: List all Relationships
For example, the Path column in the table shows the path between the leaf row ID 1 and the root row ID 5. ID 1 is a child of 2 which is a child of 3 which is a child of 4 which is a child of 5 who is a root. If this were a table of permissions, this would show how someone (a leaf) has a permission (a root) by belonging to the hierarchy of groups in between.
| ID | ParentID | Path | Depth | Leaf | Root |
| 1 | 2 | 1»2»3»4»5 | 4 | 1 | 5 |
| 2 | 3 | 2»3»4»5 | 3 | 2 | 5 |
| 3 | 4 | 3»4»5 | 2 | 3 | 5 |
| 4 | 5 | 4»5 | 1 | 4 | 5 |
| 5 | NULL | 5 | 0 | 5 | 5 |
| 6 | 7 | 6»7»5 | 2 | 6 | 5 |
| 7 | 5 | 7»5 | 1 | 7 | 5 |
| 8 | 9 | 8»9»10 | 2 | 8 | 10 |
| 9 | 10 | 9»10 | 1 | 9 | 10 |
| 10 | NULL | 10 | 0 | 10 | 10 |
Output 2: List only Leaf Nodes
The intermediate relationships are filtered out so only the leaf-root relationships remain. If this were a table of permissions, this would show how someone (a leaf) has a permission (a root) without all the noise of the groups in between.
| Leaf | Root | Path | Depth |
| 1 | 5 | 1»2»3»4»5 | 4 |
| 6 | 5 | 6»7»5 | 2 |
| 8 | 10 | 8»9»10 | 2 |
Output 3: List only Root Nodes
This result set could be used to provide a list of roots in an application for there user to filter by.
| ID | ParentID | Path | Depth | Leaf | Root |
| 5 | NULL | 5 | 0 | 5 | 5 |
| 10 | NULL | 10 | 0 | 10 | 10 |
Conclusion
Hierarchical relationships are common in data. Recursive CTEs make querying this data simple.
