Counting number of children in hierarchical SQL data
ALTER PROCEDURE [dbo].[GetEmployeesstaffwise2] ( @id varchar(30) , @unitid varchar(40)) AS ; WITH EmployeeManager AS( SELECT distinct Boss.ID, tbl_Staff.Firstname+' '+tbl_Staff.Middlename+' '+tbl_Staff.Lastname Employee , Boss.Firstname+' '+Boss.Middlename+' '+Boss.Lastname Boss FROM tbl_Staff INNER JOIN tbl_Staff AS Boss ON tbl_Staff.mgr=Boss.ID INNER JOIN tbl_Staff BigBoss ON Boss.ID=BigBoss.mgr or tbl_Staff.mgr=Boss.ID where BigBoss.mgr=@id and Boss.UnitID like'%'+@unitid+'%' and BigBoss.UnitID like'%'+@unitid+'%' and tbl_Staff .UnitID like'%'+@unitid+'%' ) select ID,employee as name,Boss as manager from EmployeeManager ID ParentID name Children 1 NULL kk 9 2 1 jin 8 3 2 NULL 3 4 2 NULL 2 5 3 NULL 1 6 2 NULL 0 7 3 NULL 0 8 4 NULL 0 9 4 NULL 0 10 5 NULL 0 ;WITH ChildrenCTE AS ( SELECT RootID = ID, I