Need SQL query to get hierarchical structure of Employees
-
I have a table with two columns:
Employee, Superior with their names in it.
Now, I need to create a query to get the overall structure of the subordinates and the subordinate's subordinates(if any) by taking an employee name.
So, If MD name is given as input, His subordinates will be manager names, Their subordinates will be department heads and their subordinates employees should come up.
I have no idea on how to do it. Please give me a query for this.
Regards,
Deepak
-
Can you provide some Sample data and expected output please? See the link in my signature for details on how to supply this.
Otherwise, I did quick Google of "T-SQL Hierarchy Query" and the first result might be what your looking for.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Employee superior
Sam Andrew
Nehal Sam
AAA Sam
BBB SamIn the above example, If i give name Andrew, The output should be
Sam
Nehal
AAA
BBBIf I give AAA as input as there are no subordinates, no result set should come up.
I hope I answered your question. Please help me with the result query. Thanks
-
Andrew doesn't have an entry in your sample data, is that intended? This exact code won't work if so (you should be able to amend it easily enough), but using the link I provided in my post as reference:
CREATE TABLE #Employee (EmployeeID INT IDENTITY(1,1),
ManagerID INT NULL,
EmployeeName VARCHAR(20));
GOINSERT INTO #Employee(ManagerID, EmployeeName)
VALUES
(NULL, 'Barry'),
(1, 'Suzi'),
(1, 'John'),
(2, 'Paul'),
(2, 'Jane'),
(3, 'Andrew'),
(6, 'Tpm'),
(6, 'Sally');
GOSELECT *
FROM #Employee;DECLARE @Manager VARCHAR(20) = 'Barry';
WITH Hierarchy AS (
SELECT E.EmployeeID,
E.EmployeeName,
E.ManagerID,
CAST(NULL AS varchar(20)) AS ManagerName,
1 AS EmployeeLevel
FROM #Employee E
WHERE E.EmployeeName = @ManagerUNION ALL
SELECT E.EmployeeID,
E.EmployeeName,
E.ManagerID,
H.EmployeeName AS ManagerName,
H.EmployeeLevel + 1 AS EmployeeLevel
FROM Hierarchy H
JOIN #Employee E ON H.EmployeeID = E.ManagerID
)
SELECT EmployeeName,
ManagerName
FROM Hierarchy H
WHERE H.EmployeeLevel != 1;GO
DROP TABLE #Employee;
GOThom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - acmedeepak - Thursday, March 9, 2017 9:19 AM
Since it sounds like you may have to work with hierarchies but don't know much about how to interrogate or traverse them, here are a few articles where you can learn about hierarchies and some very high performance techniques for using them. The examples do, in fact, cover employees and the first one covers the technique that Thom demonstrated above.
http://www.sqlservercentral.com/articles/T-SQL/72503/
http://www.sqlservercentral.com/articles/Hierarchy/94040/
http://www.sqlservercentral.com/articles/T-SQL/94570/To whet your appetite a bit, here's the small employee example used in the articles. The 2nd and 3rd article also contain code that will generate any size hierarchy you want to test with.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply