Explode components recursively to get total value

Question

TL-TR: I want to get the total value of a final item that is made of others, from which I have the buying price. Problem is semi-finished items.

Consider three types of items:

• Final item: It's made from raw items and/or semifinished items. These are the ones I want to get the total value.
• Semifinished item: It's made from raw items and/or semifinished items.
• Raw item: I have the cost of a unit of these ones.

A final item can be made, amongst other things, of a semifinished item which can be made, amongst other things, of another semifinished item and so on... on an undeterminate number of levels, until you get to all being raw items.

I know how to do this on C# but I'm interested on a pure SQL solution, that I believe is doable.

SQL Fiddle provided here:

http://sqlfiddle.com/#!6/138c3

And this is as far as I can get with the query...

SELECT BOM.output, SUM(P.price * BOM.quantity) as Total
FROM BOM
INNER JOIN 
Prices P ON P.input = BOM.Input
GROUP BY BOM.output

But of course that doesn't take semifinished prices into the sum as they don't exist in prices table.

EDIT: Another attempt, but it gives an error that grouping is not allowed in recursive queries.

WITH cte_table AS (
SELECT BOM.output, SUM(P.price * BOM.quantity) as Total
FROM BOM
INNER JOIN 
Prices P ON P.input = BOM.Input
GROUP BY BOM.output

UNION ALL
SELECT BOM.output, SUM(ISNULL(P.price,T.Total) * BOM.quantity) as Total
FROM BOM
LEFT JOIN 
Prices P ON P.input = BOM.Input
LEFT JOIN 
cte_table T ON T.output = BOM.Input
GROUP BY BOM.output
)
SELECT *
FROM cte_table

And some example of expected output (in light grey it must be calculated, in black is data):

Answer 1

You need to use the recursive query:

SQL Fiddle

Query 1:

with a as(
  SELECT BOM.output, BOM.input, P.price * BOM.quantity as price, 1 as level,
  convert(varchar(max), BOM.input) as path
  FROM BOM
  INNER JOIN 
  Prices P ON P.input = BOM.Input
  UNION ALL
  SELECT BOM.output, BOM.input, a.price * BOM.quantity as price, a.level + 1 as level,
  a.path + '/' + bom.input
  FROM a
  INNER JOIN BOM ON a.output = BOM.Input)
select output, sum(price) from a
group by output
-- select * from a

Results:

| output |                   |
|--------|-------------------|
| Item 3 | 64.32000000000001 |
| Semi 1 |                63 |
| Semi 2 |              60.4 |