hierarchy Level - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

hierarchy Level

Author

Topic

pushp82
Yak Posting Veteran

83 Posts

Posted - 2013-03-05 : 09:32:15

Hi Guys,

I got stuck in between of task i.e:
My table is as below(SUB_ID is reference of ID)

ID NAME SUB_ID
1 A 0
2 B 0
3 C 1
4 D 3
5 E 2
6 F 3

I need to get the hierarchy level of ID like
ID NAME LEVEL
1 A 1
2 B 1
3 C 2
4 D 3
5 E 2
6 F 3

How is this possible? Please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-05 : 09:37:07

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/

pushp82
Yak Posting Veteran

83 Posts

Posted - 2013-03-05 : 10:14:44

I tried CTE but was not succeed, u r perfect as always thanks visakh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-05 : 10:15:21

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources