Ken Sanchez, CEO of AdventureWorks Cycles asked you (the DBA, lead developer) to prepare a bill-of-materials (BOM) assembly report for all priced products. The product management team is going to have an offsite meeting at the Sunrise Lodge on Mount Rainier to streamline production in order to increase profit margin. You have to use a recursive CTE since some of the components require assembly as well.

USE AdventureWorks;

GO

WITH cteParts(AssembledProduct, AssemblyID, ComponentID, PerAssemblyQty, ComponentLevel) AS

(

    SELECT AssembledProduct = p.Name, b.ProductAssemblyID, b.ComponentID,

         b.PerAssemblyQty,

        1 AS ComponentLevel

    FROM Production.BillOfMaterials AS b

    INNER JOIN Production.Product p

    on p.ProductID = b.ProductAssemblyID

    WHERE ListPrice > 0.0

          AND b.EndDate IS NULL

    UNION ALL

    SELECT AssembledProduct, bom.ProductAssemblyID,

           bom.ComponentID, p.PerAssemblyQty,

           ComponentLevel + 1

    FROM Production.BillOfMaterials AS bom

        INNER JOIN cteParts AS p

        ON bom.ProductAssemblyID = p.ComponentID

        AND bom.EndDate IS NULL

)

SELECT      AssembledProduct,

            AssemblyItem=pr1.Name,

            ComponentName=pr.Name,

            ComponentLevel,

            PerAssemblyQty = CONVERT(int, PerAssemblyQty),

            Color = coalesce(pr.Color, ''),

            Weight = coalesce(convert(varchar,pr.Weight) +' '+

                     pr.WeightUnitMeasureCode,'')

FROM cteParts AS p

    INNER JOIN Production.Product AS pr

    ON p.ComponentID = pr.ProductID

    INNER JOIN Production.Product AS pr1

    ON p.AssemblyID = pr1.ProductID

ORDER BY AssembledProduct, ComponentLevel, AssemblyID, ComponentID;

GO

Partial Results: