# Multi-Level BOM Cost Rollup SQL Query Generator

> Source: https://netray.co/tools/bom-cost-rollup-recursive-cte-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Name your BOM and cost tables and get a recursive SQL query that explodes a bill of material and rolls up purchased component cost.

## Category

developer

## Audience

- ERP developer
- Cost accountant
- Estimator

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### CTE keyword

**Key:** cte_keyword

**Type:** select

**Options:**

- WITH RECURSIVE
- WITH

**Default:** WITH RECURSIVE

**Help:** SQL Server and Oracle use WITH. PostgreSQL, MySQL 8 and SQLite use WITH RECURSIVE.

#### BOM table

**Key:** bom_table

**Type:** text

**Default:** bom_line

**Help:** Generic name. Replace with your ERP BOM table.

#### Parent item column

**Key:** parent_col

**Type:** text

**Default:** parent_item

#### Component item column

**Key:** comp_col

**Type:** text

**Default:** component_item

#### Quantity per column

**Key:** qty_col

**Type:** text

**Default:** qty_per

#### Scrap percent column

**Key:** scrap_col

**Type:** text

**Default:** scrap_pct

#### Item cost table

**Key:** cost_table

**Type:** text

**Default:** item_cost

#### Item column in cost table

**Key:** item_col

**Type:** text

**Default:** item

#### Unit cost column

**Key:** cost_col

**Type:** text

**Default:** unit_cost

#### Top level item

**Key:** top_item

**Type:** text

**Default:** ASSY-1000

#### Maximum levels

**Key:** max_levels

**Type:** number

**Default:** 15

**Help:** Guards against circular BOMs.

**Blocks:**

#### Recursive BOM explosion and cost rollup

**Body:** {{cte_keyword}} bom_tree (parent_item, component_item, ext_qty, lvl) AS (
  SELECT b.{{parent_col}}, b.{{comp_col}},
         b.{{qty_col}} * (1 + b.{{scrap_col}} / 100.0), 1
  FROM {{bom_table}} b
  WHERE b.{{parent_col}} = '{{top_item}}'
  UNION ALL
  SELECT b.{{parent_col}}, b.{{comp_col}},
         t.ext_qty * b.{{qty_col}} * (1 + b.{{scrap_col}} / 100.0),
         t.lvl + 1
  FROM {{bom_table}} b
  JOIN bom_tree t ON b.{{parent_col}} = t.component_item
  WHERE t.lvl < {{max_levels}}
)
SELECT t.component_item,
       SUM(t.ext_qty) AS total_qty,
       c.{{cost_col}} AS unit_cost,
       SUM(t.ext_qty) * c.{{cost_col}} AS ext_cost
FROM bom_tree t
JOIN {{cost_table}} c ON c.{{item_col}} = t.component_item
WHERE NOT EXISTS (
  SELECT 1 FROM {{bom_table}} x
  WHERE x.{{parent_col}} = t.component_item
)
GROUP BY t.component_item, c.{{cost_col}}
ORDER BY ext_cost DESC;

#### Total rolled-up material cost

**Body:** -- Wrap the query above as a subquery named leaf_costs, then:
SELECT SUM(ext_cost) AS rolled_material_cost
FROM leaf_costs;

**Notes:** Table and column names here are generic placeholders, not any ERP's actual schema. Map them to your system's BOM and item cost tables, add effectivity date and revision filters, and remember that SQL Server may need explicit CAST on the quantity column so the recursive types match.

## Content

**Intro:** This tool writes a recursive common table expression that walks a multi-level bill of material and totals the cost of the purchased leaf components. Replace the placeholder names with your ERP's tables and run it against a copy of the data.

**Sections:**

#### How to use the generator

**Body:** Choose the CTE keyword for your database, then enter the names of your BOM table, the parent and component columns, the quantity and scrap columns, and the item cost table. Enter the top-level item you want to explode and a maximum level. The query appears on the right, ready to copy into your SQL client.

**Bullets:**

- Pick WITH or WITH RECURSIVE for your engine
- Map the column names to your ERP schema
- Set a level cap to stop circular BOMs

#### How the query works

**Body:** The anchor part selects the direct components of the top item and applies the scrap allowance as quantity times one plus scrap percent. The recursive part joins each component to its own BOM lines and multiplies the extended quantity down the levels. The final SELECT keeps only items that have no BOM of their own, which are the purchased or raw leaf items, joins them to unit cost and totals quantity times cost.

#### Reading the result

**Body:** The output lists each leaf component, the total quantity needed for one top-level unit, the unit cost and the extended cost, sorted from the largest cost down. This is a material-only rollup. Labor, machine and overhead cost come from routings and rate tables and need separate joins. Compare the total with your ERP's standard cost for the same item to find stale item costs.

#### Common mistakes

**Body:** Real BOMs have effectivity dates, revisions, phantom items and alternates, and a naive join will mix them. Add filters for the effective date and status. Also watch for components that are both bought and made, since the leaf test above treats any item without BOM lines as purchased. Finally, scrap treatment differs between systems: some store yield, not scrap, so convert before using the formula.

**Cta:** If you want BOM and cost questions answered without writing recursive SQL, Netray can connect a read-only assistant to your ERP database on your own servers.

**Faq:**

#### Why use a recursive CTE for a BOM?

**Answer:** A BOM has an unknown number of levels, so a fixed number of self-joins cannot cover every product. A recursive CTE walks the structure one level at a time until no more child lines exist, and handles any depth within the level cap.

#### How do I handle circular BOM references?

**Answer:** The level cap stops the recursion after a set number of levels, which prevents an endless loop. If the query reaches the cap, the data likely contains a loop, and you should find and fix the offending parent and component pair in your item master.

#### Does this include labor and overhead?

**Answer:** No. It rolls up purchased material only. Labor and overhead come from routing operations and rates. Join your routing and work center tables to add run time, setup time and burden to the same top-level item.

#### Which databases does it work on?

**Answer:** PostgreSQL, MySQL 8 and later, SQLite, SQL Server and Oracle all support recursive CTEs. The keyword differs, and data type handling in the recursive column differs, so test on a small BOM first.

## Search terms

recursive CTE BOM explosion SQL, BOM cost rollup SQL query, multi level bom sql, bill of materials recursive query, bom explosion sql server, standard cost rollup sql, bom cost roll up query generator

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json