T-SQL Trick: Use Recursive CTE To Get Hierarchical Data

 

In a previous article we learnt how to use CTE (Common Table Expression) to render dates in a date range. In this example we’ll use somewhat advance technique to show a data in a heirarchical form. Using the regular T-SQL query this is almost impossible to achieve it without coding in a strored procedure or using a traditional programming language. Let’s move on.

Here we have a simple table “Designations” having columns Id, Design, Id_Parent, as shown in the image below.

 

 

We have some sample data in it. The table contains the data specific to Customer Relationship Management personnel in an organization, typically in a real-estate business. The lowest rank officer is the CRO (Customer Relationship Officer) and the highest one is the G.M (General Manager), as shown in the image below.

 

 

Our requirement is to get the designations data in a hierarchical format like depicted below:

General Manager->Sr. Manager Ops.->Manager Ops.->Sr. CRO->CRO

To get it we’ll use Recursive CTE as demonstrated below:
 

WITH 
MyCTE  
AS 
( SELECT Id, Id_Parent, CAST(Desig as varchar(100))AS Hierarchy 
          FROM Designations WHERE id_Parent IS NULL
  
  UNION ALL
  
  SELECT Designations.Id, Designations.Id_Parent, CAST( (MyCTE.Hierarchy + '->' + Designations.Desig) as varchar(100) ) AS Hierarchy
		From Designations INNER JOIN MyCTE ON Designations.id_Parent = MyCTE.Id  
)

SELECT * FROM MyCTE ORDER BY MyCTE.Id ASC;

 

and here's the result.

 

Remember that this approach is a counter-part of a ‘for’ or a ‘while’ loop from a traditional programming language. First SELECT statement returns the very first row and then the next SELECT statement after UNION ALL clause provide iteration to fetch next rows till the last row is reached.

This is significant to understand that the character-type columns/expressions in both the queries should have equal length and that is achieved by using CAST() function from within SQL SELECT statements.

Note that the query fetches the data in a top-down hierarchy – starting from General Manager and then down the hierarchy up to the rank in question for each row. If we want to see them in a bottom-up order then we’ve to slightly amend the above query as:

WITH 
MyCE  
AS 
( SELECT Id, Id_Parent, CAST(Desig as varchar(100))AS Hierarchy 
          FROM Designations WHERE id_Parent IS NULL
  
  UNION ALL
  
  SELECT Designations.Id, Designations.Id_Parent, CAST( (Designations.Desig + '->' +  
       MyCTE.Hierarchy) as varchar(100) ) AS Hierarchy
  From Designations INNER JOIN MyCTE ON Designations.id_Parent = MyCTE.Id    
)
SELECT * FROM MyCTE ORDER BY MyCTE.Id ASC;

and here’s the modified query result: