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

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.

• Tweet
• Share
• Share
• Share

Related Post