In SQL Server a common table expression can be used to calculate totals / subtotals of a table containing hierarchical data:
-- Create test data:
if object_id('tempdb..#Hierarchy') is not null
begin
drop table #Hierarchy
end
create table #Hierarchy (
Id int not null,
ParentId int null,
Amount int not null
)
insert into #Hierarchy (Id, ParentId, Amount) values
(1, null, 100)
, (2, 1, 100)
, (3, 1, 100)
, (4, 1, 100)
, (5, 4, 100)
, (6, 4, 100)
, (7, 4, 100)
, (8, 7, 100)
;with CTE (ParentId, Id, Amount, IsParent)
as
(
-- Add all records as "anchor", because foreach record we are going to calculate the total amount.
select h.ParentId
, h.Id
, h.Amount
, 0 as IsParent
from #Hierarchy h
union all
-- Recursively add parent records until root parent foreach record in "anchor".
select h.ParentId
, h.Id
, h.Amount
, 1 as IsParent
from CTE c
join #Hierarchy h on c.ParentId = h.Id
)
select c1.Id
, c1.ParentId
, sum(c1.Amount) as TotalAmount
, case when sum(c1.IsParent) > 0 then 1 else 0 end as IsParent
from CTE c1
group by c1.Id, c1.ParentId
Result
