Ordering one bicycle means ordering more than one row of parts. A bill of materials describes how components fit inside other components. A recursive CTE follows those relationships and carries the required quantity down each path.
Store the Relationship as an Edge
Use a parts table for identity and description. Use a components table for the parent-child relationship and quantity per parent. One child can belong to several assemblies, so the design isn't necessarily one simple tree.
The relationship table must support that reuse. A composite primary key prevents the same parent-child edge from being stored twice without an explicit business reason.
I check the meaning of Quantity before writing recursion. It must be the amount needed for one parent, not a previously expanded total. Keep the unit of measure consistent or provide a conversion rule.
Mixing pieces and weight silently multiplies incompatible values. The sample below uses invented quantities for practice. It establishes the edge structure without pretending to model a complete manufactured product.
CREATE TABLE #Parts(PartId int PRIMARY KEY, PartName nvarchar(60) NOT NULL);
CREATE TABLE #Components
(
ParentId int NOT NULL,
ChildId int NOT NULL,
Quantity decimal(12,4) NOT NULL CHECK (Quantity > 0),
PRIMARY KEY(ParentId,ChildId)
);
INSERT #Parts VALUES (1,N'Bicycle'),(2,N'Wheel'),(3,N'Spoke'),(4,N'Frame');
INSERT #Components VALUES (1,2,2),(1,4,1),(2,3,24);Carry Quantity Through Each Level
The anchor selects direct children of the requested root. The recursive member finds children of the preceding component. Multiply the accumulated quantity by the new edge quantity at each step.
Explicit casts keep recursive column types identical. SQL Server requires that agreement between anchor and recursive members. A multiplication expression can otherwise infer a different decimal precision and stop compilation.
The path records the visited identifiers using delimiters. That prevents confusing part 1 with part 11 during membership checks. The level shows depth relative to the root's direct children.
Neither column replaces a stable business key. The same child can appear on several valid paths, each contributing quantity. Don't discard those paths before deciding whether the output needs detail or a rolled-up requirement.
DECLARE @Root int = 1;
WITH Explosion AS
(
SELECT c.ChildId, 1 AS DepthLevel,
CAST(c.Quantity AS decimal(28,8)) AS RequiredQuantity,
CAST('/' + CONVERT(varchar(11),@Root) + '/'
+ CONVERT(varchar(11),c.ChildId) + '/' AS varchar(max)) AS PartPath
FROM #Components AS c WHERE c.ParentId = @Root
UNION ALL
SELECT c.ChildId, e.DepthLevel + 1,
CAST(e.RequiredQuantity * c.Quantity AS decimal(28,8)),
CAST(e.PartPath + CONVERT(varchar(11),c.ChildId) + '/' AS varchar(max))
FROM Explosion AS e
JOIN #Components AS c ON c.ParentId = e.ChildId
WHERE CHARINDEX('/' + CONVERT(varchar(11),c.ChildId) + '/',e.PartPath) = 0
)
SELECT p.PartName, e.RequiredQuantity, e.DepthLevel, e.PartPath
FROM Explosion AS e JOIN #Parts AS p ON p.PartId = e.ChildId
ORDER BY e.PartPath
OPTION (MAXRECURSION 100);Read the Bill of Materials Detail Before Summing
The bill of materials path explains why a component is required. In the sample output, Spoke shows 48 at level 2, which is two wheels times 24 spokes. Two branches can contain the same part without representing a duplicate error. For purchasing, aggregate RequiredQuantity by ChildId after recursion.
For assembly instructions, retain the path and level. Those are different outputs from the same relationship. Choose the required grain before placing DISTINCT or GROUP BY into the final query.
I review a shared component case alongside a simple chain. It exposes accidental deduplication and quantity mistakes quickly. A leaf-only purchase report also needs an explicit rule for components without children. Decide whether you buy or build each assembly.
Don't combine assembled subcomponents and raw children into one purchase total accidentally. The recursion finds relationships. The manufacturing policy decides what to order.
Walk the Bill of Materials Upward for Where-Used
To find assemblies affected by a component, start with edges whose ChildId matches that part. Walk upward by matching the parent's identity against another edge's child. This answers where a part is used.
The reverse traversal has the same cycle risk as the forward one. Keep a delimited path and a recursion limit instead of trusting the direction to guarantee termination.
The result lists ancestors, not a production impact quantity by itself. A root production order and its planned units are another input. Join that information only after establishing the structural relationships.
Repeating an ancestor through different branches can be legitimate. Preserve the path detail explaining the impact. Roll it up only when purchasing or change review needs a summary.
DECLARE @Part int = 3;
WITH WhereUsed AS
(
SELECT ParentId, 1 AS DepthLevel,
CAST('/' + CONVERT(varchar(11),@Part) + '/'
+ CONVERT(varchar(11),ParentId) + '/' AS varchar(max)) AS PartPath
FROM #Components WHERE ChildId = @Part
UNION ALL
SELECT c.ParentId, w.DepthLevel + 1,
CAST(w.PartPath + CONVERT(varchar(11),c.ParentId) + '/' AS varchar(max))
FROM WhereUsed AS w
JOIN #Components AS c ON c.ChildId = w.ParentId
WHERE CHARINDEX('/' + CONVERT(varchar(11),c.ParentId) + '/',w.PartPath) = 0
)
SELECT p.PartName, w.DepthLevel, w.PartPath
FROM WhereUsed AS w JOIN #Parts AS p ON p.PartId = w.ParentId
OPTION (MAXRECURSION 100);Treat Cycle Protection as a Data Check
The path predicate stops a traversal from revisiting an identifier. That protects the query but also omits the cyclic edge. Report invalid cycles through a separate validation process rather than calling the shortened result complete.
A self-reference CHECK can prevent one direct cycle. It doesn't prevent a longer loop through several components. Those need validation across the relationship graph.
MAXRECURSION places another bound on execution depth. A limit error is a signal to investigate, not a reason to set zero automatically. Zero removes the recursion limit.
Which maximum assembly depth does your business allow? Use that knowledge to choose the guard. A bicycle containing itself is impressive packaging, but it is a poor procurement specification.
Index Both Traversal Directions
The parent-child key supports downward traversal by ParentId. A where-used query benefits from an index beginning with ChildId. Include Quantity if that direction needs the edge amount.
Evaluate the indexes against actual lookup and maintenance work. The recursive member repeats relationship lookups. Efficient edge access matters more than sorting part names for a pretty final grid.
Inspect the actual plan and reads on representative assemblies. Deep chains and wide assemblies stress different parts of the query. Keep quantity casts wide enough for multiplication, then handle overflow as a data-design issue.
Increasing recursion depth won't fix numeric overflow. The graph shape and numeric domain need separate limits, documented before a large production explosion depends on them.
CREATE INDEX IX_Components_Child ON #Components(ChildId,ParentId) INCLUDE(Quantity);Tie the Bill of Materials to an Approved Revision
Versioned designs need an effective-date or revision rule on the edges. Otherwise, an old production order can expand through today's component structure. Filter the approved revision before recursion and keep that selection in the output.
A correct traversal over the wrong revision produces a convincing wrong answer. The structure must represent the product configuration required by the request.
Use the bill of materials to explain every required component through its path and multiplied quantity. Test shared parts, leaves, deep branches, and invalid cycles. Keep purchasing policy separate from structural expansion.
A recursive CTE makes the relationship easy to follow. The useful result is the one whose quantities and revision can be traced back to an approved root product.
For production tables, enforce the parent and child references with validated foreign keys. The temporary fixture concentrates on traversal. Those temporary tables don't enforce foreign key declarations, so don't mistake the demonstration for a complete integrity design. Keep orphan detection and cycle validation in the real loading process alongside the structural keys.
Related reading on this blog: Making Recursive Parent-Child Queries Efficient and Replacing a Cursor with a Common Table Expression.
A parts explosion is not a list of descendants, it is a quantity calculation along approved paths.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
