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). 


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

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  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

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

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.

Now over to you:
A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..