Recursive Queries Using CTE, get from parent

Question

I have a table

Node | NoteParent | Num
  A  |     Root   | 10      
  B  |     A      | 20
  C  |     A      | 30
  G  |     B      | 40
  D  |     B      | 50
  E  |     G      | 70
  F  |     C      | 60

I have a question that how I can use CTE to Sum the Num of node depend on the link of its to another node. For example, I have node B have Num is 20, node B is the parent of node G and node D, also node G is the parent of node E so I will use all the num of related nodes 20(B) + 40(G) + 50(D) + 70(E) = 180

The result would be:

Node | NoteParent |  Num  |  SUM 
  A  |    Root    |  10   |  280
  B  |     A      |  20   |  180   
  C  |     A      |  30   |   90 
  G  |     B      |  40   |  110
  D  |     B      |  50   |   50
  E  |     G      |  70   |   70
  F  |     C      |  60   |   60

Adriaan Stander · Accepted Answer · 2013-08-06 04:51:28Z

5

How about something like

DECLARE @MyTable TABLE
    ([Node] varchar(1), [NoteParent] varchar(4), [Num] int)
;

INSERT INTO @MyTable
    ([Node], [NoteParent], [Num])
VALUES
    ('A', 'Root', 10),
    ('B', 'A', 20),
    ('C', 'A', 30),
    ('G', 'B', 40),
    ('D', 'B', 50),
    ('E', 'G', 70),
    ('F', 'C', 60)


;WITH Vals AS (
        SELECT  mt.Node TopNode,
                mt.NoteParent TopNoteParent,
                *
        FROM    @MyTable mt
        UNION ALL
        SELECT  v.TopNode,
                v.TopNoteParent,
                m.*
        FROM    @MyTable m  INNER JOIN
                Vals v ON   v.Node = m.NoteParent
)
SELECT  TopNode,
        TopNoteParent,
        SUM(Num) [SUM]
FROM    Vals
GROUP BY    TopNode,
        TopNoteParent
ORDER BY 1

SQL Fiddle DEMO

answered Aug 6, 2013 at 4:51

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Recursive Queries Using CTE, get from parent

1 Answer 1

SQL Fiddle DEMO

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related