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
No comments:
Post a Comment