Building hierarchy using Recursive CTE

Introduction

Building hirarchy in the sense of hierarchy of manager, employee though both are an
employee of an enterprise but both are related or depend on hirarchical level.

Retrieved hierarchy can also be useful to display it properly in many of the controls
like Menu, Tree view and many more in the list.

Relation SQL doesn't handle hierarchical data quite efficiently than XML does. There are several different ways to handle hierarchical data, But the familiar one is an adjacency model and it looks like below. The data is actually stored in DB

Now what if you want to find a hierarchy for an employee no. 8, with the following result:

Write your T-SQL query using Recursive CTE, like:

WITH CTE(EmployeeName,empcode,managercode) AS
(
SELECT EmployeeName,empcode,managercode from EMP where empcode=8
UNION ALL
SELECT e.EmployeeName,e.empcode,e.managercode
from EMP e 
INNER JOIN CTE c ON e.empcode = c.managercode
)
SELECT * FROM CTE

Query Explanation

With the first query in union all, it just simply retrieves record having employeeID 8 (one record), further second query using the result of the first query to join with and by joining again, it will loop until it finds that employee 1 does not have any manager to join with.

Story Behind

Let me tell you one interesting thing behind writing this trick is that I have questioned[^] once to solve this query and I was also curious to finding a solution for.