Sum Of Child Levels Total In A Hierarchy

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 Budget and Revised Budget columns. I've incl

Solution 1:

Your ItemNo appears to have the item hierarchy embedded in it. However, the first value should be '10' rather than '10.01'. If this were fixed, the following query would work:

select i.ID, i.ParentItemID, i.ItemNo, i.ItemName,
       sum(isum.Budget) as Budget,
       sum(isum.RevisedBudget) as RevisedBudget
from item i leftouterjoin
     item isum
     on isum.ItemNo like i.ItemNo+'%'groupby i.ID, i.ParentItemID, i.ItemNo, i.ItemName;

EDIT:

To do this as a recursive CTE requires a somewhat different approach. The idea of the recursion is to generate a separate row for each possible value for an item (that is, everything below it), and then to aggregate the values together.

The following does what you need, except it puts the levels in the reverse order (I don't know if that is a real problem):

WITH HierarchicalCTE AS
(
    SELECT ID, ParentItemID, ItemNo, ItemName,
           Budget, RevisedBudget, 0AS LEVEL
    FROM Item i
    UNION ALL
    SELECT i.ID, i.ParentItemID, i.ItemNo, i.ItemName,
           cte.Budget, cte.RevisedBudget,
           cte.LEVEL + 1FROM HierarchicalCTE cte join
         Item i
         ON i.ID = cte.ParentItemID
)
select ID, ParentItemID, ItemNo, ItemName,
       sum(Budget) as Budget, sum(RevisedBudget) as RevisedBudget,
       max(level)
from HierarchicalCTE
groupby ID, ParentItemID, ItemNo, ItemName;