Rollup value in hierarchy table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Rollup value in hierarchy table

Author

Topic

dungla
Starting Member

2 Posts

Posted - 2008-09-10 : 00:32:41

Hi Experts,

I've 2 tables below:
Table1
MyID
ParentID (Self link to MyID)
Code
=====
Table2 (store value of smallest level item in Table1, the item that doesn't has any child)
MyID
Value
=======

I've succeed query a hierarchy view of Table1. Now I want to add value from Table2 to the query associated with MyID from Table1. The value will be roll up to parent value.
Example:
Table1
MyID ParentID Code
1 NULL First
2 NULL Second
3 1 Third
4 3 Fourth
5 3 Fifth
6 1 Sixth
7 2 Seventh
Table2
MyID Value
4 10
5 20
6 30
7 40

Result should be:
MyID Value
1 60
--3 30
----4 10
----5 20
--6 30
2 70
--7 40

The question is: How can I roll up value from child to parent in hierarchy view?

LAD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-09 : 03:07:00

Use a recursive Common Table Expression (cte).

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources