Recursively calculate product weights in product tree

I’m developing a software for Consumer price index calculation and one step of all calculation is to calculate product weights in product category. This should be done in SQL server side. I have MS SQL Server 2008. The product catalog are stored in a tree:

|id|ProductCode|ParentId|Weight|
--------------------------------
|1 |01         |NULL    |0     |
|2 |01.1       | 1      |0     |
|3 |01.1.1     | 2      |0     |
|4 |01.1.1.101 | 3      |0.14  |
|5 |01.1.1.102 | 3      |0.1   |
|6 |01.1.1.103 | 3      |0.25  |
|7 |01.1.1.104 | 3      |0.02  |
|8 |01.1.2     | 2      |0     |
|9 |01.1.2.201 | 8      |0.05  |
|10|01.1.2.202 | 8      |0.3   |
--------------------------------

It is simplified structure of my table, so i need to calculate weights for categories.

For example:

• weight for category ‘01.1.1’ = sum of product weight 01.1.1.101 + 01.1.1.102 + 01.1.1.103 + 01.1.1.104 =0.51
• for category ‘01.1.2’ = sum of 01.1.2.201 + 01.1.2.201 = 0.35
• for category 01.1 = sum of 01.1.1 +01.1.2 =0.51 +0.35 = 0.86
• for category 01 = sum of 01.1 + 01.2 (not showed in my example)

And my problem actually is, I can’t calculate weights of parent categories.

Maybe someone can help me to solve my problem? Thank you!

Answer

I think Joakim’s answer does not give the correct result.

Perhaps this will work:

-- Use a safe database
use tempdb

if object_id('Products') is not null drop table Products

create table Products (
    id int,
    ProductCode varchar(100),
    ParentId int,
    Weight float
)

-- Original sample data
insert into Products (id,ProductCode, ParentId, Weight)
SELECT 1 ,'01',        NULL    ,'0'      UNION ALL
SELECT 2 ,'01.1'       , '1'      ,'0'      UNION ALL
SELECT '3 ','01.1.1',' 2      ','0     ' UNION ALL
SELECT '4 ','01.1.1.101',' 3      ','0.14  ' UNION ALL
SELECT '5 ','01.1.1.102',' 3      ','0.1   ' UNION ALL
SELECT '6 ','01.1.1.103',' 3      ','0.25  ' UNION ALL
SELECT '7 ','01.1.1.104',' 3      ','0.02  ' UNION ALL
SELECT '8 ','01.1.2',' 2      ','0     ' UNION ALL
SELECT '9 ','01.1.2.201',' 8      ','0.05  ' UNION ALL
SELECT '10','01.1.2.202',' 8      ','0.3   ' 

-- Extra test data
insert into Products (id,ProductCode, ParentId, Weight)
SELECT 11 ,'01.2',        1    ,'1.1'      

-- Calculate result
select b.ProductCode, SUM(a.Weight) as WeightSum
from Products a
join Products b on b.ProductCode = left(a.ProductCode, len(b.ProductCode))  and len(b.ProductCode) < len(a.ProductCode)
group by b.ProductCode

Result:

ProductCode WeightSum
01  1,96
01.1    0,86
01.1.1  0,51
01.1.2  0,35