Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick

Building hierarchy using Recursive CTE

Rate me:
Please Sign up or sign in to vote.
4.57/5 (9 votes)
5 Jan 2011CPOL1 min read 54K   8   17
Building hirarchy in the sense of hirarchy of manager, employee though both are an employee of an enterprise but both are related or depend on hierarchical level.


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:


SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
India India
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!!!

Comments and Discussions

 
GeneralReason for my vote of 5 This is a great tip! Very useful to... Pin
Estys30-Dec-10 4:53
Estys30-Dec-10 4:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.