I hope use Recursive CTE to find Total for all childrencount and parentcount and path and level and currentlevel like this
Id ParentId Name Path Level CurrentLevel ChildrenCount ParentCount
1 NULL a 1 4 1 3 0
3 1 c 3,1 3 2 2 1
4 3 d 4,3,1 2 3 1 2
5 4 f 5,4,3,1 1 4 0 3
2 NULL b 2 5 1 5 0
6 2 g 6,2 4 2 4 1
7 6 h 7,6,2 3 3 3 2
8 7 i 8,7.6.2 2 4 2 3
9 8 j 9,8,7,6,2 1 5 0 4
10 8 k 10,8,7,6,2 1 5 0 4
I tried the following code, but I don't know how to get childrencount and parentcount and path and level and currentlevel, how to code it dynamically calculate it.
CREATE TABLE #temp([id] int, [parentid] int null,[name] varchar(5));
INSERT INTO #temp ([id], [parentid], [name])
VALUES ('1', null,'a')
, ('2', null,'b')
, ('3', '1','c')
, ('4', '3','d')
, ('5', '4','e')
, ('6', '2','f')
, ('7', '6','h')
, ('8', '7','i')
, ('9', '8','j')
, ('10', '8','k')
WITH AllChildrens as
(
SELECT p.*, CAST(P.Id AS VarChar(Max)) as [Path]
FROM Department P where p.ParentId is null
UNION ALL
SELECT P1.*, CAST(P1.Id AS VarChar(Max)) + ',' + M.[Path]
FROM Department P1
INNER JOIN AllChildrens M
ON M.Id = P1.ParentId
)
SELECT Id,ParentId,Name,Path From AllChildrens order by Id
One option is to use the data type hierarchyid
Example
;with cteP as (
Select ID
,parentid
,Name
,HierID = convert(hierarchyid,concat('/',ID,'/'))
From #Temp
Where parentid is null
Union All
Select ID = r.ID
,parentid = r.parentid
,Name = r.Name
,HierID = convert(hierarchyid,concat(p.HierID.ToString(),r.ID,'/'))
From #Temp r
Join cteP p on r.parentid = p.ID)
Select ID
,parentid
,Name
,Path = HierID.ToString()
,Depth = ( Select max(HierID.GetLevel() ) from cteP where HierID.ToString() like A.HierID.ToString()+'%') - HierID.GetLevel()
,Lvl = HierID.GetLevel()
,ChildCnt = ( Select count(*) from cteP where HierID.ToString() like A.HierID.ToString()+'%') -1
,ParentCnt = len(HierID.ToString()) - len(replace(HierID.ToString(),'/','')) - 2
From cteP A
Order By A.HierID
Results