Using CTE in Sql Server 2005 / 2008

Declare @table table (id int,name varchar(50),parentid int)
Insert into @table values (1,'Director',NULL)
Insert into @table values (4,'City Manager Fsd',3)
Insert into @table values (5,'Area Manager Fsd',4)
Insert into @table values (2,'County Manager',1)
Insert into @table values (3,'Country Manager 2',1)
Insert into @table values (6,'City Manager ISB',2)
Insert into @table values (7,'Co-director',NULL)
Insert into @table values (8,'Country Head',7)
;

with CTE(id ,name,parentid,level) as

(



Select id,name,parentid ,1 as level from @table t where parentid is null

union all

Select td.id,td.name,td.parentid,level+ 1 as level from @table td inner join cte on td.parentid = cte.id

)


select * from CTE


Find answers of your questions in this advance technique of SQL Server