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