Aggregation with a parent child hierarchy
571034Dec 6 2007 — edited Nov 6 2010Hi
I have 2 tables, hierarchy and data
hierarchy has 2 columns parent and child
data contains values for the lowest rungs of the hierarchy described.
What i'd like to do is, using the hierarchy described, sum the lowest rung values up to their parent values and so on.
Quick example:
Hierarchy
child parent
1 null
2 1
3 1
4 2
5 2
Data
hiearchyid value
3 10
4 20
5 30
output required
1 60
2 50
3 10
4 20
5 30
The last 3 rows are just the 2nd table so i can just union those in but how would i work out the first two values using sql... have tried combinations of connect by, analytics (partition by etc) and rollup but to no avail...
Any help would be appreciated..
Cheers
Alex