How to recursively summarize a parent / child hierarchy

Question

I have a data table that looks like this:

UnitID UnitParentID ScoreCount ScoreSum Level 7112 72 292 1 7120 7112 72 308 2 7139 7112 24 82 2 7150 7112 166 586 2 23682 7112 104 414 2 7100 7112 272 1016 2 23691 7112 94 300 2 23696 7112 24 80 2 23700 23696 184 594 3 23694 23691 24 64 3 23689 7120 88 390 3 7148 23696 112 420 3 7126 7120 32 132 3 7094 7120 96 332 3 7098 7094 64 240 4 23687 7094 16 62 4 

What I want to do is recursively add from the lowest hierarchy to the highest so that the numbers below collapse into their parent. Thus, the parent would add any children to the existing values, from the top of the tree to the top.

In this example, the last two lines will remain unchanged because they have no children. The device ID 7094 will have a score of 96 (base) + 64 + 16 (2 children) for a new total of 176 (and the same logic for the scale). The rest at level 3 will remain unchanged because they have no children. I believe that I need to start from the bottom so that the levels above have the correct values ​​for any children.

If someone can point me to a good source where I could learn this, I would be very grateful.

Answer 1

With CTE AS (SELECT 7112 unitid, NULL UnitParentId, 72 ScoreCount, 292 ScoreSum, 1 level UNION ALL SELECT 7120 unitid, 7112 UnitParentId, 72 ScoreCount, 308 ScoreSum, 2 levels UNION ALL SELECT 7139 unitid, 7112 UnitParentId, 24 ScoreCount, 82 ScoreSum , 2 levels UNION ALL SELECT 7150 unitid, 7112 UnitParentId, 166 ScoreCount, 586 ScoreSum, 2 levels UNION ALL SELECT 23682 unitid, 7112 UnitParentId, 104 ScoreCount, 414 ScoreSum, 2 levels UNION ALL SELECT 7100 unitid, 7112 UnitParentId, 272 ScoreCount, 1016 ScoreSum, 2 levels UNION ALL SELECT 23691 unitid, 7112 UnitParentId, 94 ScoreCount, 300 ScoreSum, 2 levels UNION ALL SELECT 23696 unitid, 7112 UnitParentId, 24 ScoreCount, 80 ScoreSum, 2 levels UNION ALL SELECT 23700 unitid, 23696 UnitParentId, 184 ScoreCount, 594 ScoreSum, 3 levels UNION ALL SELECT 23694 unitid, 23691 UnitParentId, 24 ScoreCount, 64 ScoreSum, 3 levels UNION ALL SELECT 23689 unitid, 7120 UnitParentId, 88 ScoreCount, 390 ScoreSum, 3 levels UNION ALL SELECT 7148 unitid, 23696 UnitParentId, 112 ScoreCount, 420 ScoreSum, 3 levels UNION ALL SELECT 7126 unitid, 7120 UnitParentId, 32 ScoreCount, 132 ScoreSum, 3 levels UNION ALL SELECT 7094 unitid, 7120 UnitParentId, 96 ScoreCount, 332 ScoreSum, 3 levels UNION ALL SELECT 7098 unitid, 7094 UnitParentId, 64 ScoreCount, 240 points, 4 levels UNION ALL SELECT 23687 unitid, 7094 UnitParentId, 16 ScoreCount, 62 ScoreSum, 4 levels), RECURSIVECTE AS ( SELECT unitid, CONVERT (NVARCHAR (MAX), convert (nvarchar (20), unitid)) PARENTLIST, ScoreCount FROM CTE WHERE UnitParentId IS NULL

UNION ALL

SELECT C.unitid, CONVERT (NVARCHAR (MAX), convert (nvarchar (20), R.PARENTLIST) + ',' + convert (nvarchar (20), C.unitid)), C.ScoreCount
FROM RECURSING R INNER JOIN CTE C ON R.unitid = C.UnitParentId)

SELECT C.unitid, R.ScoreCount FROM CTE C CROSS APPLIES (SELECT SUM (ScoreCount) ScoreCount FROM RESOURCES R WHERE CHARINDEX (convert (nvarchar (20), C.UNITID), R.PARENTLIST, 0)> 0) P