use recursive ctes
;With CTE
AS
(
SELECT ID,NAME,SUB_ID, CAST(1 AS int) AS Level
FROM table t
LEFT JOIN table t1
On t1.ID = t.SUB_ID
WHERE t1.ID IS NULL
UNION ALL
SELECT t.Id,t.NAME,t.SUB_ID,c.Level+1
FROM CTE c
JOIN table t
ON t.SUB_ID = c.ID
)
SELECT ID,NAME,Level
FROM CTE
OPTION (MAXRECURSION 0)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
hierarchy Level
Posted - 03/05/2013 : 09:32:15
