How to Implement a Hierarchy using SQL Server?

Hierarchy can be implemented using common table expression (CTE).

WITH UserHierarchy ([user_id], [manager_id],[Level]) AS

(

SELECT [user_id],user_ap_mngd_by as manager,0

FROM [user_profile] usrtmp1

WHERE usrtmp1.[user_id]=1

UNION ALL

SELECT usrtmp2.[user_id],user_ap_mngd_by as manager, [Level] + 1

FROM [user_profile] usrtmp2

INNER JOIN UserHierarchy ON

usrtmp2.user_ap_mngd_by = UserHierarchy.[user_id]

)

SELECT * FROM UserHierarchy



Author: InApp
We are a custom software development company offering Testing Services, Application Development, Mobility Solutions & more. Customers: Startups - Fortune 500

Leave a Reply

2 × four =