Recently, I had a complicated scenario while creating reports. I need to have each level, which may be the sum of all children (in the hierarchy) in addition to any values, which are set against that value itself for the amount column. I failed to produce the expected results, using Linq. Thanks to SQL server, for coming to the rescue. Using CTE, it's easy to produce what exact result I want. In this blog, I am going to share, how to do cumulative sum on a hierarchical level, using SQL Server.
To discuss the scenario, let's create a table in 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 populate the 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 look, as shown below.
Image 1:Parent Child Hierarchical structure
Query
Using CTE (Common Table Expressions), we will first flatten the hierarchical data. Afterwards, we will use 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
Image 2: Query result
After running this query, you will get the cumulative sum of the child amount at each node.
I hope, like me, you got your solution, using this query.
Reference