Using a recursive common table expression, to calculate totals and subtotals of children and grandchildren in a hierarchy
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