MSSQL Recursive Hierarchial Query

Question

5.00/5 (1 vote)

See more:

Hi Everyone,

I'm quiet new to MS SQL and I have problem in creating a hierarchial query that would display an employee and the nearest manager to the topmost manager.

For example, If i have a table with this syntax,

Create table Employee (EmpID int, Manager_ID int NULL, Title nvarchar(30));

with the following values,

1 NULL CEO
2 1    VP
3 2    Senior Manager
4 3    Project Manager
5 4    Developer

For the Employee with ID 5, I need to display data like

5 4 Project Manager
5 3 Senior Manager
5 2 VP
5 1 CEO

The following query will give me only one level of hierarchy ie; 5 4 project manager

WITH ManagerHierarchy (EmpID, ManagerID, Title)
AS
(
-- Anchor member definition
    SELECT e.EmpID, e.ManagerID, e.Title 
    FROM dbo.Employee AS e
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.EmpID, e.ManagerID, e.Title
    FROM dbo.Employee AS e
    INNER JOIN ManagerHierarchy AS d
        ON e.ManagerID = d.EmpID
)
-- Statement that executes the CTE
SELECT EmpID, ManagerID, Title
FROM ManagerHierarchy

Is there any way I can iterate this process so that I can get the Employee's nearest Manager to the topmost Manager?

Any Inputs will be greatly appreciated. :)

Thanks in Advance
Ranganath P

Posted 21-Mar-11 7:23am

rangiemax

Updated 21-Mar-11 7:26am

Wendelius

v2

Add a Solution

Comments

Wendelius 21-Mar-11 13:26pm

Pre tags added

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Accepted Answer · 2011-03-21T07:50:00

One way of doing this could be something like:

WITH ManagerHierarchy (EmpID, empid2, ManagerID, Title)
AS
(
-- Anchor member definition
    SELECT e.EmpID, e.EmpID, e.Manager_ID, e.Title 
    FROM dbo.Employee AS e
    WHERE not exists(select 1 from Employee e2 where e2.Manager_ID = e.EmpID) --Manager_ID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.EmpID, null,  e.Manager_ID, e.Title
    FROM dbo.Employee AS e
    INNER JOIN ManagerHierarchy AS d
        ON e.EmpID = d.ManagerID
)
-- Statement that executes the CTE
SELECT EmpID, ManagerID, max(empid2) over (partition by 1), Title
FROM ManagerHierarchy

I reversed the tree to get the last element in the tree as a starting point and then recursively upwards. Now the empid2 contains the employee number if it's the last in chain, otherwise null. And in the calling portion I took max (well it doesn't matter if its min or avg since null's don't count) and to use max overt the whole result set i partitioned it with a constant.

I'm sorry the explanation is quite poor (it's starting get late where I am) but hopefully the code is clearer :)