SQL SERVER: CTE RECURSIVE QUERY TO GET PARENT CHILD HIERARCHY WITH LEVELS

SQL SERVER: CTE RECURSIVE QUERY TO GET PARENT CHILD HIERARCHY WITH LEVELS
Introduction: In this article I am going to explain how to create parent-child hierarchy(Continent-> Country-> State-> City) with levels using recursive common table expression (CTE).

In previous articles i have explained How to use CTE recursive query to get employee manager hierarchy with level and Using CTE to get all dates between two specified dates and Multiple sql server queries to get all dates between two dates and Check column existence before adding new column in table and Row_number(), rank(), dense_rank(), ntile() ranking functions

Description: While working with database we often store parent and child id in same table. For example category id and sub category Id, employee id and his manager id etc.

Here in this article I am taking an example where continents and their countries and state of that countries and cities of that states are stored in same table. And suppose it is required to get all of them in hierarchy e.g. Continent-> Country-> State-> City, then CTE is very useful in such case because of its recursive capability.

Implementation: Let’s understand by an example: Create a dummy table using the script below:

IF OBJECT_ID(‘tempdb.dbo.#tbHierarchy’, ‘U’) IS NOT NULL
DROP TABLE #tbHierarchy;

GO
CREATE TABLE #tbHierarchy
(
Id INT
,[Name] VARCHAR(20)
,ParentId INT
)
Insert some dummy data using the script below:
GO
INSERT INTO #tbHierarchy(Id, [Name], ParentId)
VALUES
(1, ‘Europe’, NULL)
,(2, ‘Asia’, NULL)
,(3, ‘Africa’, NULL)
,(4, ‘France’, 1)
,(5, ‘India’, 2)
,(6, ‘China’, 2)
,(7, ‘Zimbabwe’, 3)
,(8, ‘Hong Kong’, 6)
,(9, ‘Beijing’, 6)
,(10, ‘Shanghai’,6)
,(11, ‘Chandigarh’, 5)
,(12, ‘Mumbai’, 5)
,(13, ‘Delhi’, 5)
,(14, ‘Haryana’, 5)
,(15, ‘Gurgaon’, 14)
,(16, ‘Panchkula’, 14)
,(17, ‘Paris’, 4)
,(18, ‘Marseille’, 4)
,(19, ‘Harare’, 7)
,(20, ‘Bulawayo’, 7);

Check table data

Recursive CTE query to get Continent-> Country-> State-> City relationship hierarchy with levels.

;WITH MyCTE
AS
(
– anchor
SELECT Id, [Name], ParentId,1 AS [Level],
CAST(([Name]) AS VARCHAR(MAX)) AS Hierarchy
FROM #tbHierarchy t1
WHERE ParentId IS NULL
UNION ALL
–recursive member
SELECT t2.id, t2.[Name], t2.ParentID,M.[level] + 1 AS [Level],
CAST((M.Hierarchy + ‘->’ + t2.Name) AS VARCHAR(MAX)) AS Hierarchy
FROM #tbHierarchy AS t2
JOIN MyCTE AS M ON t2.ParentId = M.Id
)

SELECT * FROM MyCTE

Explanation: The base record for the CTE is obtained by the first select query above UNION ALL. It gets all the ParentIds which don’t have ParentId i.e. NULL value. This means they are the continents so their Level is set to 1.

Second select query below UNION ALL is executed recursively to get results and it will continue until it returns no rows. Countries will be assigned Level 2, States will be assigned Level 3 and Cities will be assigned Level 4 and hierarchy is created as you can see in the final output.