• A combination between the 2 solution can do as well :>)

    SELECT *

    FROM

    (

    SELECT [PRODUCT_NAME],[PART_NUMBER],[Qty],

    sum(case when Product_Name = 'BOM1' then isnull(Extended_Cost,0) else 0 end ) as Bom1Cost,

    sum(case when Product_Name = 'BOM2' then isnull(Extended_Cost,0) else 0 end ) as Bom2Cost

    FROM [CURRENTBOM] WHERE [PRODUCT_NAME] IN ('BOM1','BOM2')

    group by [PRODUCT_NAME],[PART_NUMBER],[Qty]

    ) AS DATA

    PIVOT

    (SUM([Qty]) FOR [PRODUCT_NAME] IN ([BOM1],[BOM2])) as PVT1

    order by

    Part_Number