Thanks Koen,

With the following configuration:

tabA

Id ParentId

1001 NULL

1002 1001

1003 1001

1004 NULL

1005 NULL

tabB

Id Value

1001 123

1002 25

1003 30

1004 85

1005 218

your query will give results as:

IDValue

1001369 [123+123+123 which is wrong]

100485

1005218

which means it is repeating the value 123 thrice(once for each iteration while expected result is 123+25+30=178)

I got the correct query. It should be like this:

;with CTE as

(

select a.id as 'P_ID', a.p_id as 'C_ID', b.value

from #tempa

inner join #temp1bona.id = b.id

where a.p_id IS NULL

union

select a.p_id as 'P_ID', a.id as 'C_ID', b.value

from #tempa

inner join #temp1bona.id = b.id

where a.p_id IS NOT NULL

)

selectP_ID, SUM(value)

fromCTE

GROUP BY P_ID

It will give the desired result as:

P_ID(No column name)

1001178 [123+25+30]

100485

1005218

I don't know if we can write it in more efficient way.