Hierarchical Summary UPDATE Query for MSSQL

/* CumulativeSum Column Calculation on Hierarchical Table Write Update query for "CumulativeSum" column You can see an example result in remark area. Simply you will cumulative summary over CumulativeSum column on hierarchical table. */ CREATE TABLE #Tree ( Id int, RootId int, FullName varchar(200), QTY int, CumulativeSum int ) INSERT #Tree Select 1, -1, 'ROOT 1', 0, 0 INSERT #Tree Select 2, 1, 'SUB ITEM 1.1', 9, 0 INSERT #Tree Select 3, 1, 'SUB ITEM 1.2', 3, 0 INSERT #Tree Select 4, 3, 'SUB ITEM 1.2.1', 1, 0 INSERT #Tree Select 5, 3, 'SUB ITEM 1.2.2', 7, 0 INSERT #Tree Select 6, 1, 'SUB ITEM 1.3', 5, 0 INSERT #Tree Select 7, 6, 'SUB ITEM 1.3.1', 2, 0 INSERT #Tree Select 8, 7, 'SUB ITEM 1.3.1.1', 18, 0 INSERT #Tree Select 9, -1, 'ROOT 2', 0, 0 INSERT #Tree Select 10, -1, 'ROOT 3', 0, 0 INSERT #Tree Select 11, 10, 'SUB ITEM 3.1', 1, 0 INSERT #Tree Select 12, 11, 'SUB ITEM 3.2', 10, 0 INSERT #Tree Select 13, 12, 'SUB ITEM 3.3', 4, 0 INSERT #Tree Select 14, 13, 'SUB ITEM 3.3.1', 2, 0 INSERT #Tree Select 15, 13, 'SUB ITEM 3.3.2', 5, 0 /* FOR EXAMPLE 1 -1 ROOT 1 0 45 2 1 SUB ITEM 1.1 9 9 3 1 SUB ITEM 1.2 3 11 4 3 SUB ITEM 1.2.1 1 1 5 3 SUB ITEM 1.2.2 7 7 6 1 SUB ITEM 1.3 5 25 7 6 SUB ITEM 1.3.1 2 20 8 7 SUB ITEM 1.3.1.1 18 18 */ Select * From #Tree DROP TABLE #Tree