Recursive CTE to find Total for all children parent and depth

Question

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

Interesting that the code you've posted doesn't even pass a syntax check. And what does "Depth" mean, when you are showing 3 & 4 for the 2 top level elements? In fact how are you wishing to calculate all the rest of the values? Its not obvious from looking at the data. — Dale K, May 10 '21 at 02:55
"Ancestor" and "Descendant" might make more sense than "Parent" and "Children", although it isn't obvious without an explanation. Why is `CurrentDepth` never `1`? — HABO, May 10 '21 at 03:12
@DaleK depth is the level ,It means how many levels are there in the sub-item . — beiduoan, May 10 '21 at 03:23
@DaleK I'm sorry that my mistake has caused trouble for you, I have accurately repaired my data — beiduoan, May 10 '21 at 03:41

score 3 · Accepted Answer · answered May 10 '21 at 03:38

3

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

answered May 10 '21 at 03:38

John Cappelletti

79,615
7
44
66

Thats awesome!! PS ParentAccount doesn't match desired results - but maybe OP is wrong? – Dale K May 10 '21 at 03:46
why id=5 row depth=0 but lvl=4 It should be depth=1 ? – beiduoan May 10 '21 at 03:58
@beiduoan just add one to it if you want it 1 based instead of 0 based... – Dale K May 10 '21 at 04:03
1

@beiduoan You made several changes and I decided that you could adjust when you settled on the final results. – John Cappelletti May 10 '21 at 04:14
@JohnCappelletti I have confirmed my results and you can adjust the latest code. Thank you – beiduoan May 10 '21 at 06:14
You can never terminate too much or too early: `;;with ... Order By A.HierID;;`. – HABO May 10 '21 at 13:05
@HABO OK... that was funny :) – John Cappelletti May 10 '21 at 13:12

Recursive CTE to find Total for all children parent and depth

1 Answers1