Performing Recursive SQL Query (SQL Server 2005)

Question

I have this table:

ID_Articulo ID_Componente   Tipo    Cantidad
1           5               2       1.5
5           3               1       3
1           6               1       6
2           3               1       3.5
6           8               2       4.2
8           9               1       2

I need to find the SUM of Cantidad field for a given ID_Articulo and Tipo=1. For instance, for ID_Articulo=1 should be 1.5*3 + 6 + 6*4.2*2 = 60.9.

The number to level (deep) are variable for each ID_Articulo.

Can this be donw with a SQL query? My database is SQL Server 2005.

Aditional info

Fields ID_Articulo AND ID_Compenente are related to the same table Articulos. Thats why the data is recursive. So, for the given example I have:

Rec #1 is not Tipo=1, but relates ID 1 with ID 5
Rec #2 relates ID 5 with ID 3 and Tipo=1 So, I have 1.5 (of Rec #1) * 3
Rec #3 relates ID 1 with ID 6 and Tipo=1, so i have 6
Rec #4 is from another ID
Rec #5 relates ID 6 with ID 8 but Tipo!=1 so i don't sum
Rec #6 relates ID 8 and ID 9 and Tipo=1, so I have 6 * 4.2 * 2

Final solution

This is the final code (Some field names are different):

;WITH CTE AS (
SELECT b.ID_Articulo, ID_Componente, Tipo, Cantidad,   
      CAST(Cantidad AS DECIMAL(6,2)) AS partialSum
FROM Arbol b inner join articulos a on a.ID_Articulo=b.ID_Componente
WHERE b.ID_Articulo = 2716

UNION ALL

SELECT t.ID_Articulo, t.ID_Componente, t.Tipo, t.Cantidad,
       CAST(c.partialSum * t.Cantidad AS DECIMAL(6,2)) AS partialSum
FROM (SELECT b.ID_Articulo, ID_Componente, A.Tipo, Cantidad FROM Arbol b inner join articulos a on a.ID_Articulo=b.ID_Componente inner join Articulos a2 on a2.ID_Articulo=b.ID_Articulo where a2.Tipo<>'I') as t
INNER JOIN CTE AS c ON c.ID_Componente = t.ID_Articulo
)
SELECT SUM(partialSum)
FROM CTE   
WHERE Tipo = 'I'

Thanks @giorgos-betsos for final solution

Answer 1

You can use the following recursive CTE to get expected result:

;WITH CTE AS (
   SELECT ID_Articulo, ID_Componente, Tipo, Cantidad,   
          CAST(Cantidad AS DECIMAL(6,1)) AS partialSum
   FROM mytable 
   WHERE ID_Articulo = 1

   UNION ALL

   SELECT t.ID_Articulo, t.ID_Componente, t.Tipo, t.Cantidad,
          CAST(c.partialSum * t.Cantidad AS DECIMAL(6,1)) AS partialSum
   FROM mytable AS t
   INNER JOIN CTE AS c ON t.ID_Articulo = c.ID_Componente
)
SELECT SUM(partialSum)
FROM CTE   
WHERE Tipo = 1

What the above recursion does, is that it returns all branches emanating from ID_Articulo = 1, along with the cumulative product of the multiplication of Cantidad values:

 ID_Articulo    ID_Componente   Tipo    Cantidad    partialSum
------------------------------------------------------------------
 1              5               2       1.5         1.5
 1              6               1       6.0         6.0
 6              8               2       4.2         25.2
 8              9               1       2.0         50.4
 5              3               1       3.0         4.5

The result is calculated using the SUM on partialSum only for those rows where Tipo = 1.

Demo here