SQLServer 2008 to find the sum of all rows and the sum of row components

Question

/ * I have a problem with a query in SQLServer 2008 to find the sum of all rows and the sum of parts of rows. I used ROW_NUMBER (), outer_apply, but I can not solve the problem. I have been browsing for a few days on your stack_overflow website, but I cannot find anything like it. Now I don’t even know how to do it. Can someone help me ???

Input data: section number, starting Node, final Node, region, section - the distance between two nodes. Each node belongs to a specific area. * /

ABCD -------------------------------- Section Initial Final Area Number Node Node ================================ 1 0 1 0 2 1 2 5 3 2 3 3.1 4 3 4 3.7 5 4 5 2.7 6 5 6 0 7 6 7 4.1 8 7 8 0 9 8 9 2.8 10 9 10 4.6 11 10 11 3.2 12 11 12 3 13 12 13 5 14 13 14 3.5 15 1 15 3 16 15 16 5.6 17 16 17 4.7 18 17 18 2.8 19 6 19 0 20 19 20 3.8 21 20 21 5 22 19 22 2.9 23 8 23 4.4 

/ * It is necessary to calculate the cumulative area (Area Cum), which belongs to each node on the network. The result should be * /

 ABCDEF ----------------------------------------------------- Section Initial Final Area Area Description Number Node Node Cum ===================================================== 1 0 1 0 72.9 =SUM(D1:D23) 2 1 2 5 56.8 =SUM(D2:D14)+SUM(D19:D23) 3 2 3 3.1 51.8 =SUM(D3:D14)+SUM(D19:D23) 4 3 4 3.7 48.7 =SUM(D4:D14)+SUM(D19:D23) 5 4 5 2.7 45 =SUM(D5:D14)+SUM(D19:D23) 6 5 6 0 42.3 =SUM(D6:D14)+SUM(D19:D23) 7 6 7 4.1 30.6 =SUM(D7:D14)+D23 8 7 8 0 26.5 =SUM(D8:D14)+D23 9 8 9 2.8 22.1 =SUM(D9:D14) 10 9 10 4.6 19.3 =SUM(D10:D14) 11 10 11 3.2 14.7 =SUM(D11:D14) 12 11 12 3 11.5 =SUM(D12:D14) 13 12 13 5 8.5 =SUM(D13:D14) 14 13 14 3.5 3.5 =SUM(D14) 15 1 15 3 16.1 =SUM(D15:D18) 16 15 16 5.6 13.1 =SUM(D16:D18) 17 16 17 4.7 7.5 =SUM(D17:D18) 18 17 18 2.8 2.8 =SUM(D18) 19 6 19 0 11.7 =SUM(D19:D22) 20 19 20 3.8 8.8 =SUM(D20:D21) 21 20 21 5 5 =SUM(D21) 22 19 22 2.9 2.9 =SUM(D22) 23 8 23 4.4 4.4 =SUM(D23) 

Answer 1

Using a recursive CTE , you can do something like this:

 WITH RCTE AS ( SELECT *, NULL S FROM Table1 UNION ALL SELECT t.*, COALESCE(rS,r.SectionNumber) S FROM Table1 t INNER JOIN RCTE r ON r.FinalNode = t.InitialNode ) , CTE2 AS ( SELECT S, SUM(Area) AS AreaCum FROM RCTE GROUP BY S ) SELECT t.*, COALESCE(c.AreaCum, t.Area) AreaCum FROM Table1 t LEFT JOIN CTE2 c ON t.SectionNumber = cS 

SQLFiddle DEMO