Recursive CTE – Bill of Materials – BOM View

/*

Based on a table Product.Products and Product.BillOfMaterials.

*/

/*

SELECT * FROM Product.Products WHERE ID = 738

SELECT * FROM Product.vBillOfMaterials WHERE ProductAssemblyID = 738 ORDER BY BOMLevel

SELECT * FROM Product.vBillOfMaterialsName WHERE ProductAssemblyID = 738 ORDER BY BOMLevel

*/

CREATE VIEW [Product].[vBillOfMaterials]

AS

WITH BOM_cte 

AS

(

SELECT b.ProductID AS ProductAssemblyID, ID, ProductID, ComponentID, ItemNo, Quantity, LastUpdate, LastUpdateBy, AlternativeAccy, 0 AS BOMLevel 

FROM Product.BillOfMaterials b

UNION ALL

SELECT cte.ProductAssemblyID, b.ID, b.ProductID, b.ComponentID, b.ItemNo, b.Quantity, b.LastUpdate, b.LastUpdateBy, b.AlternativeAccy, BOMLevel + 1 AS BOMLevel

        FROM [BOM_cte] cte

            INNER JOIN [Product].[BillOfMaterials] b 

            ON b.ProductID = cte.[ComponentID]

)

SELECT BOM.* FROM BOM_cte BOM