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
  (
     CategotyId        INT
     ,ParentCategotyId INT
     ,CategotyOrder INT
     ,CategotyName     VARCHAR(20)
  ) 

GO

-- Inserting samples records
INSERT INTO CTEMaxLevelTab
 (
 CategotyId
 ,ParentCategotyId
 ,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 

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


UNION ALL

SELECT
  CategotyId      AS Child
  ,ParentCategotyId    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 ( ParentCategotyId = Child ) 

)

-- Viewing Data
SELECT
  *
FROM   cteLevels

GO

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


UNION ALL

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

)

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

GO