Count number of left and right child

Select * FROM MLM



--    exec pr_CntLandR '1'
Create proc pr_CntLandR @UID varchar(10)
as
with Traverse as
(
  select
    UID, cast(POS as varchar(max)) Nodes
  from
    dbo.MLM c
  where
    P_ID = 0
  union all
  select
    c.UID, r.Nodes + CAST(c.POS as varchar)
  from
    dbo.MLM c
  join
    Traverse r on r.UID = c.P_ID
)
select
  t1.UID,-- u.P_ID,
  (select COUNT(*) from Traverse t2 where t1.Nodes + '1' = LEFT(t2.Nodes, len(t1.Nodes) + 1)) LeftCount,
  (select COUNT(*) from Traverse t2 where t1.Nodes + '2' = LEFT(t2.Nodes, len(t1.Nodes) + 1)) RightCount
from
  Traverse t1
join
  dbo.MLM u on u.UID = t1.UID
 where t1.UID=@UID
 order by
  t1.UID
option (maxrecursion 0)






Note: 1-> Left & 2-> Right childs.