Using CTE Recursively to get the hierachy from Parent child stored in same table (Continent-> Country-> State-> City)
In this article I will explain how to write a query to create parent-child hierarchy (Continent-> Country-> State-> City) with levels using recursive common table expression (CTE).
While working with database we often store parent and child id in same table. Let's take the example of Continents, Countries, State/Province and City. They are stored in same table, and we have to get all of them in hierarchy e.g. Continent-> Country-> State-> City.
Let's begin and understand this via an example:
Create a 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
SELECT * FROM #tbHierarchy
Id | Name | ParentId |
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 |
Recursive CTE query to get Continent-> Country-> State-> City relationship hierarchy with levels.
;WITH ContinentCTE
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 ContinentCTE AS M ON t2.ParentId = M.Id
)
SELECT * FROM ContinentCTE
The Top parent record (i.e. Continent name) 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.
Id | Name | ParentId | Level | Hierarchy |
1 | Europe | NULL | 1 | Europe |
2 | Asia | NULL | 1 | Asia |
3 | Africa | NULL | 1 | Africa |
7 | Zimbabwe | 3 | 2 | Africa->Zimbabwe |
19 | Harare | 7 | 3 | Africa->Zimbabwe->Harare |
20 | Bulawayo | 7 | 3 | Africa->Zimbabwe->Bulawayo |
5 | India | 2 | 2 | Asia->India |
6 | China | 2 | 2 | Asia->China |
8 | Hong Kong | 6 | 3 | Asia->China->Hong Kong |
9 | Beijing | 6 | 3 | Asia->China->Beijing |
10 | Shanghai | 6 | 3 | Asia->China->Shanghai |
11 | Chandigarh | 5 | 3 | Asia->India->Chandigarh |
12 | Mumbai | 5 | 3 | Asia->India->Mumbai |
13 | Delhi | 5 | 3 | Asia->India->Delhi |
14 | Haryana | 5 | 3 | Asia->India->Haryana |
15 | Gurgaon | 14 | 4 | Asia->India->Haryana->Gurgaon |
16 | Panchkula | 14 | 4 | Asia->India->Haryana->Panchkula |
4 | France | 1 | 2 | Europe->France |
17 | Paris | 4 | 3 | Europe->France->Paris |
18 | Marseille | 4 | 3 | Europe->France->Marseille |
Comments
Post a Comment