Hi ashaharka,

Check this test query.

SQL

DECLARE @Test AS TABLE(id INT,parent INT,name CHAR(5))
INSERT INTO @Test VALUES(1,0,'admin')
INSERT INTO @Test VALUES(2,1,'a1')
INSERT INTO @Test VALUES(3,1,'b1')
INSERT INTO @Test VALUES(4,2,'c2')
INSERT INTO @Test VALUES(5,2,'d2')
INSERT INTO @Test VALUES(6,3,'e3')
INSERT INTO @Test VALUES(7,3,'f3')
INSERT INTO @Test VALUES(8,4,'g4')
INSERT INTO @Test VALUES(9,5,'h5')

;WITH CTE (Id,Name,Parent, Level) AS 
(
	SELECT s.id,s.name,0,1
	FROM @Test s
	WHERE parent = 0
	UNION ALL
	SELECT t.id,t.name,t.parent,c.level + 1
	FROM CTE c
	JOIN @Test t on c.id = t.parent
)
SELECT * FROM CTE ORDER BY Id

Output

Id  Name  Parent  Level

1    admin   0        1

2    a1         1        2

3    b1         1        2

4    c2         2        3

5    d2         2        3

6    e3         3        3

7    f3         3         3

8    g4         4        4

9    h5         5        4