DECLARE @BOM TABLE (Product varchar(11), Part varchar(11))

INSERT INTO @BOM SELECT 'StockCode1', 'StockCode2'

UNION ALL SELECT 'StockCode1', 'StockCode3'

UNION ALL SELECT 'StockCode1', 'StockCode4'

UNION ALL SELECT 'StockCode1', 'StockCode5'

UNION ALL SELECT 'StockCode2', 'StockCode10'

UNION ALL SELECT 'StockCode2', 'StockCode11'

UNION ALL SELECT 'StockCode2', 'StockCode12'

UNION ALL SELECT 'StockCode12', 'StockCode20'

DECLARE @T1 TABLE (Product varchar(11), Part varchar(11))

DECLARE @T2 TABLE (Product varchar(11), NewPart varchar(11), OldPart varchar(11))

INSERT INTO @T1 SELECT Product, Part FROM @BOM

INSERT INTO @T2 SELECT A.Product, B.Part, A.Part FROM @T1 A INNER JOIN @BOM B ON B.Product = A.Part

WHILE (SELECT COUNT(*) FROM @T2) > 0

BEGIN

INSERT INTO @T1 SELECT Product, NewPart FROM @T2

DELETE A FROM @T1 A WHERE EXISTS (SELECT * FROM @T2 Z WHERE Z.Product = A.Product AND Z.OldPart = A.Part)

DELETE A FROM @T1 A WHERE A.Product IN (SELECT Z.OldPart FROM @T2 Z)

DELETE FROM @T2

INSERT INTO @T2 SELECT A.Product, B.Part, A.Part FROM @T1 A INNER JOIN @BOM B ON B.Product = A.Part

END

SELECT Product, Part FROM @T1 ORDER BY Product, Part