Click here to Skip to main content
15,867,288 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

 
GeneralUr tip got good vote, but my answer didn't :) just kidding Pin
senguptaamlan30-Dec-10 18:48
senguptaamlan30-Dec-10 18:48 
GeneralReason for my vote of 5 this one is nice Pin
Pranay Rana30-Dec-10 16:46
professionalPranay Rana30-Dec-10 16:46 
GeneralThanks Estys you liked it. and thanks for pointing me out on... Pin
Hiren solanki30-Dec-10 5:41
Hiren solanki30-Dec-10 5:41 
Thanks Estys you liked it. and thanks for pointing me out on my mistake, It's corrected accordingly.
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 
GeneralThanks Manfred. Pin
Hiren solanki30-Dec-10 3:44
Hiren solanki30-Dec-10 3:44 
GeneralReason for my vote of 5 Great tip! Pin
Manfred Rudolf Bihy30-Dec-10 3:43
professionalManfred Rudolf Bihy30-Dec-10 3:43 
Generalvery nice vote of 5 Pin
vikas amin4-Jan-11 10:02
vikas amin4-Jan-11 10:02 
GeneralRe: very nice vote of 5 Pin
Hiren solanki4-Jan-11 17:53
Hiren solanki4-Jan-11 17:53 
GeneralA modest question Pin
Manfred Rudolf Bihy30-Dec-10 4:04
professionalManfred Rudolf Bihy30-Dec-10 4:04 
GeneralRe: A modest question Pin
Hiren solanki30-Dec-10 5:38
Hiren solanki30-Dec-10 5:38 
GeneralRe: A modest question Pin
Manfred Rudolf Bihy30-Dec-10 6:52
professionalManfred Rudolf Bihy30-Dec-10 6:52 
GeneralRe: A modest question Pin
Hiren solanki30-Dec-10 18:13
Hiren solanki30-Dec-10 18:13 
GeneralRe: A modest question Pin
Manfred Rudolf Bihy30-Dec-10 23:06
professionalManfred Rudolf Bihy30-Dec-10 23:06 
GeneralRe: A modest question Pin
Hiren solanki30-Dec-10 23:08
Hiren solanki30-Dec-10 23:08 
GeneralMy vote of 5! Pin
Manfred Rudolf Bihy30-Dec-10 3:46
professionalManfred Rudolf Bihy30-Dec-10 3:46 
GeneralRe: My vote of 5! Pin
Hiren solanki30-Dec-10 3:50
Hiren solanki30-Dec-10 3:50 
GeneralRe: My vote of 5! Pin
Manfred Rudolf Bihy30-Dec-10 3:59
professionalManfred Rudolf Bihy30-Dec-10 3:59 

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.