Click here to Skip to main content
15,845,436 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have table EmployeeMaster like
SQL
EmpID   Name    ReportingEmployeeID
EMP001	A	EMP002
EMP002	B	EMP004
EMP003	C	EMP008
EMP004	D	EMP003
EMP008	E	EMP233
EMP233	F	EMP122
EMP122	G	EMP006


now i want to see Hierarchy of employee like first EmpID then his ReportingEmpId then his ReportingEmpId and so on.....

from above table i want output like
SQL
EMP001
EMP002
EMP004
EMP003
EMP008
EMP233
EMP122
EMP006


What is the Query for this Hierarchy
Posted

There are tons of articles available regarding hierarchal queries. Try below google search

Hierarchical query CTE SQL Server[^]
 
Share this answer
 
Comments
ErBhati 6-Jun-14 2:46am    
Thanks for reply but i have already search but not getting proper Query what i want.
WITH UserCTE AS (
SELECT EmpCode, EmpName, ReportingManagerCode,0 AS steps
FROM EMP_MASTER
WHERE ReportingManagerCode = 'EMP001'
UNION ALL

SELECT mgr.EmpCode, mgr.EmpName, mgr.ReportingManagerCode, usr.steps +1 AS steps
FROM UserCTE AS usr
INNER JOIN EMP_MASTER AS mgr
ON usr.ReportingManagerCode = mgr.EmpCode
)
SELECT * FROM UserCTE AS u
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900