multi level hierarchical data in sql

 In this article, we are going to see, multi-level hierarchical data in SQL

IF OBJECT_ID('tempdb..#tblLocations') IS NOT NULL DROP TABLE #tblLocations

CREATE TABLE #tblLocations (Id INT IDENTITY(1,1),ParentId INT, Name VARCHAR(20));

GO

INSERT INTO #tblLocations (ParentId, Name) VALUES

(NULL, 'West'),

(1, 'WA'),

(2, 'Seattle'),

(NULL, 'East'),

(4, 'NY'),

(5, 'New York'),

(1, 'NV'),

(7, 'Las Vegas'),

(2, 'Vancouver'),

(4, 'FL'),

(5, 'Buffalo'),

(1, 'CA'),

(10, 'Miami'),

(12, 'Los Angeles'),

(7, 'Reno'),

(12, 'San Francisco'),

(10, 'Orlando'),

(12, 'Sacramento');

GO

;WITH MYCTE AS (

  SELECT Id, Name, 0 AS TreeLevel,CAST('' AS VARCHAR) + CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER ( PARTITION BY ParentId ORDER BY Id) ) AS TreePath 

  FROM #tblLocations T1

  WHERE ParentId IS NULL

  UNION ALL

  SELECT T2.Id, T2.Name, TreeLevel + 1, 

  CAST(TreePath AS VARCHAR) + '.' + CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (PARTITION BY ParentId ORDER BY T2.Id )) AS TreePath

  FROM #tblLocations T2

  INNER JOIN MYCTE ITMS ON ITMS.Id = T2.ParentId

)

-- Note: The 'replicate' function is not needed. Added it to give a visual of the results.

SELECT Id, REPLICATE('.', TreeLevel * 4)+Name 'Name', TreeLevel, TreePath

FROM  MyCTE 

ORDER BY CAST('/' + REPLACE(TreePath, '.', '/') + '/' AS HIERARCHYID)