Tsql looping father-son relationship between tables

Question

I have a table like this:

table item
(
   id    int,
   quantity float,
   father int, -- refer to item itself in case of subitem
)

I need to sum al quantity plus sons quantity like this way:

select i.id, max(i.quantity)+sum(ft.quantity) as quantity
from item i
left join item ft on ft.id=i.id
group by i.id

My trouble is because relationship between father-son is recursive so I would like to sum also his grandfather quantity and so on... and i don't know the maximum deepness, than I can not join many times.

What can i do? Thank you.

Answer 1

You have to use a recursive CTE. Somthing like this:

;WITH FathersSonsTree
AS
(
  SELECT Id, quantity, 0 AS Level
  FROM Items WHERE fatherid IS NULL
  UNION ALL
  SELECT c.id, c.quantity, p.level+1
  FROM FathersSonsTree p
  INNER JOIN items c ON c.fatherid = p.id
 ), ItemsWithMaxQuantities
AS
(
  SELECT *,
  ROW_NUMBER() OVER(PARTITION BY level 
                    ORDER BY quantity DESC) rownum
  FROM FathersSonsTree
  )
SELECT 
  ID,  
  (SELECT MAX(Quantity) 
   FROM FathersSonsTree t3 
   WHERE t3.level = t1.level
  ) + 
  ISNULL((SELECT SUM(t2.Quantity) 
   FROM FathersSonsTree t2
   WHERE t1.level - t2.level = 1), 0)
FROM FathersSonsTree t1
ORDER BY ID;

SQL Fiddle Demo

This will give you something like:

| ID | QUANTITY |
-----------------
|  1 |       10 |
|  2 |       20 |
|  3 |       20 |
|  4 |       20 |
|  5 |       32 |
|  6 |       32 |
|  7 |       32 |
|  8 |       32 |