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
EmployeeID
| ManagerID
| EmployeeName
|
1
| 0
| Hiren Solanki
|
2
| 1
| Gaurang Devluk
|
3
| 2
| Matthew Dave
|
4
| 5
| Dhaval Raninga
|
5
| 3
| Altaf Khan
|
6
| 2
| Virang Patel
|
7
| 3
| Rajesh Thakur
|
8
| 4
| Tusshar Surve
|
Now what if you want to find a hierarchy for an employee no. 8, with the following result:
EmployeeName
| EmployeeID
| ManagerID
|
Tusshar Surve
| 8
| 4
|
Dhaval Raninga
| 4
| 2
|
Gaurang Devluk
| 2
| 1
|
Hiren Solanki
| 1
| 0
|
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.
Thanks for your time, guys.
He is a Smart IT devloper with Few years of Expeariance But having Great command on ASP.net,C#,SQL Query,SSRS,Crystal Reports
Apart from that He Loves multimedia work too, Master of Adobe photoshop, Illustrator, CSS , HTML and all things.
He is Currently working in Microsoft Dynamics CRM and Having Nice Expearince with CRM. CRM Rocks!!!