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')

Adam SchachabovPosted Dec 26, 2016, 7:03 AM
I mean correctly call ParentId instead of ProductCategoryId.