Parent Child Hierarchical Cumulative Sum using sql server

Recently I had a complicated scenario while generating reports. I need to have each level be the sum of all children (in the hierarchy) in addition to any values set against that value itself for the amount column. I failed to produce expected results using Linq, Thanks to SQL server, it comes to the rescue. Using CTE it's a bit easy to produce what exact result I want. In this article, I am going to share, how to Cumulative Sum on Hierarchical level using SQL server.
CREATE TABLE ProductCategories(
    Id int IDENTITY(1,1) NOT NULL,
    Amount float NOT NULL,
    ProductCategoryId int NULL,
    Name VARCHAR(150) NULL)
Insert data into ProductCategories to generate hierarchical structure
INSERT ProductCategories VALUES (100, NULL, N'A1')
INSERT ProductCategories VALUES (90, NULL, N'A2')
INSERT ProductCategories VALUES (80, NULL, N'A3')
INSERT ProductCategories VALUES (20, 1, N'A11')
INSERT ProductCategories VALUES (30, 1, N'A12')
INSERT ProductCategories VALUES (10, 1, N'A13')
INSERT ProductCategories VALUES (70, 2, N'A21')
INSERT ProductCategories VALUES (50, 2, N'A22')
INSERT ProductCategories VALUES (5, 4, N'A11.1')
INSERT ProductCategories VALUES (10, 4, N'A11.2')
INSERT ProductCategories VALUES (15, 5, N'A12.1')
INSERT ProductCategories VALUES (20, 5, N'A12.2')
INSERT ProductCategories VALUES (25, 9, N'A11.1.1')
INSERT ProductCategories VALUES (30, 9, N'A11.1.2')
INSERT ProductCategories VALUES (35, 10, N'A11.2.1')
INSERT ProductCategories VALUES (40, 10, N'A11.2.2')
The Hierarchy will be look a like below image
parent-child-Hierarchical-cumulative-sum-sql-server
Parent Child Hierarchical structure

Query

Using CTE (Common Table Expressions) we will first flatten the Hierarchical data, then after we will inner join ProductCategories table CTE generated table.
;with C as
(
  select T.id,
         T.Amount,
         T.id as RootID
  from ProductCategories T
  union all
  select T.id,
         T.Amount,
         C.RootID
  from ProductCategories T
    inner join C 
      on T.ProductCategoryId = C.id
)

select T.id,
       T.ProductCategoryId,
       T.Name,
       T.Amount,
       S.AmountIncludingChildren
from ProductCategories T
  inner join (
             select RootID,
                    sum(Amount) as AmountIncludingChildren
             from C
             group by RootID
             ) as S
    on T.id = S.RootID
order by T.id
option (maxrecursion 0)

Result

Hierarchical-cumulative-sum-sql-server
Query Result

After running this query, You will get Cumulative sum of child amount at each nodes.

Hope as like me, you got your solution using this query!😇

Reference