Hi,
you can try with below approach-
declare @t table(Parent_ID char(1),Child_ID char(1))
insert @t values ('A','B'),('C','D'),('B','C'),('B','E'),('E','F')
select t1.Parent_ID,t1.Child_ID,isnull(t2.count+t3.t3_cnt,1) Level from @t t1 left join
(select Parent_ID,count(Child_ID)count from @t group by Parent_ID) t2
on t1.Parent_ID=t2.Parent_ID
left join (select...