Click here to Skip to main content
Click here to Skip to main content

Building hierarchy using Recursive CTE

By , 5 Jan 2011
Rate this:
Please Sign up or sign in to vote.

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.

License

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

About the Author

Hiren solanki
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 Pinmembersenguptaamlan30-Dec-10 18:48 
GeneralReason for my vote of 5 this one is nice PinmemberPranay Rana30-Dec-10 16:46 
GeneralThanks Estys you liked it. and thanks for pointing me out on... PinmemberHiren 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... PinmemberEstys30-Dec-10 4:53 
GeneralThanks Manfred. PinmemberHiren Solanki30-Dec-10 3:44 
GeneralReason for my vote of 5 Great tip! PinmvpManfred R. Bihy30-Dec-10 3:43 
Generalvery nice vote of 5 Pinmembervikas amin4-Jan-11 10:02 
GeneralRe: very nice vote of 5 PinmemberHiren Solanki4-Jan-11 17:53 
GeneralA modest question PinmemberManfred R. Bihy30-Dec-10 4:04 
GeneralRe: A modest question PinmemberHiren Solanki30-Dec-10 5:38 
GeneralRe: A modest question PinmemberManfred R. Bihy30-Dec-10 6:52 
GeneralRe: A modest question PinmemberHiren Solanki30-Dec-10 18:13 
GeneralRe: A modest question PinmemberManfred R. Bihy30-Dec-10 23:06 
GeneralRe: A modest question PinmemberHiren Solanki30-Dec-10 23:08 
GeneralMy vote of 5! PinmemberManfred R. Bihy30-Dec-10 3:46 
GeneralRe: My vote of 5! PinmemberHiren Solanki30-Dec-10 3:50 
GeneralRe: My vote of 5! PinmemberManfred R. Bihy30-Dec-10 3:59 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140421.2 | Last Updated 5 Jan 2011
Article Copyright 2010 by Hiren solanki
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid