Depth First Strategy with Hierarchical data using CTE (Common Table Expression) - SQL Server

CTE (Common Table Expression) is best feature to get the hierarchy of the data and hierarchical depth.
Using CTE we can also get the data easily by level wise with parent and child relation.

Recently when i working with stored procedure, at that time i was need to develop the logic to get the data in hierarchical order.
I did it with CTE easily. Today i am going to demonstrate CTE with small examples.

Creating Objects

IF ( Object_id('CTEMaxLevelTab') > 0 )
  DROP TABLE CTEMaxLevelTab

GO

CREATE TABLE CTEMaxLevelTab
  (
     CategoryId        INT
     ,ParentCategoryId INT
     ,CategotyOrder INT
     ,CategotyName     VARCHAR(20)
  ) 

GO

-- Inserting samples records
INSERT INTO CTEMaxLevelTab
 (
 CategoryId
 ,ParentCategoryId
 ,CategotyOrder
 ,CategotyName
 )
values
  ( 1,NULL,1,'Category - 1'),
  ( 2,1,1,'Category - 2'),
  ( 3,2,1,'Category - 3'),
  ( 4,3,1,'Category - 4'),
  ( 5,4,1,'Category - 5'),
  ( 6,2,2,'Category - 6'),
  ( 7,6,1,'Category - 7'),
  ( 8,1,2,'Category - 8')
GO 

-- Viewing data in table 
SELECT
  *
FROM   CTEMaxLevelTab

GO 

Let us look for the below script which will return the data in level Order

-- Creating CTE
;WITH cteLevels
AS
(
SELECT
  CategoryId   AS Child
  ,ParentCategoryId AS Parent
  ,CategotyOrder as CategotyOrder
  ,1        AS [Level],
  CAST (REPLICATE('.',1) + CategotyName as varchar(25) )  as [Categoty Name]
FROM   CTEMaxLevelTab 
WHERE CategoryId = 1


UNION ALL

SELECT
  CategoryId      AS Child
  ,ParentCategoryId    AS Parent
  ,CTEMaxLevelTab.CategotyOrder as CategotyOrder
  ,[Level] + 1 AS [Level],
   CAST (REPLICATE('.',[Level] + 1) + CategotyName as varchar(25) )  as [Categoty Name]
FROM   CTEMaxLevelTab
       INNER JOIN cteLevels
         ON ( ParentCategoryId = Child ) 

)

-- Viewing Data
SELECT
  *
FROM   cteLevels

GO

Below script which will return the data in hierarchical order

-- Creating CTE
;WITH cteLevels
AS
(
SELECT
  CategoryId                                              AS Child
  ,ParentCategoryId                                       AS Parent
  ,CategotyOrder                                          AS CategotyOrder
  ,1                                                      AS [Level]
  ,CAST(CategoryId AS VARCHAR(MAX))                       AS [Order]
  ,CAST (Replicate('.', 1) + CategotyName AS VARCHAR(25)) AS [Categoty Name]
FROM   CTEMaxLevelTab
WHERE  CategoryId = 1 


UNION ALL

SELECT
  CategoryId                                                        AS Child
  ,ParentCategoryId                                                 AS Parent
  ,CTEMaxLevelTab.CategotyOrder                                     AS CategotyOrder
  ,[Level] + 1                                                      AS [Level]
  ,[Order] + '.' + CAST(CategoryId AS VARCHAR(MAX))                 AS [Order]
  ,CAST (Replicate('.', [Level] + 1) + CategotyName AS VARCHAR(25)) AS [Categoty Name]
FROM   CTEMaxLevelTab
       INNER JOIN cteLevels
         ON ( ParentCategoryId = Child ) 

)

-- Viewing Data
SELECT
  *
FROM   cteLevels  
order by [Order]; 

GO

You can see how CTE can help to get the data and the hierarchical relation of records.

Get easy with CTE!