For more of a visual impact....
--===== Conditionally drop the test table to make reruns in SSMS easier.
-- (Note that this is NOT a part of the solution.)
IF OBJECT_ID('TempDB..#dmBOM','U') IS NOT NULL DROP TABLE #dmBOM;
--===== Create and populate the test table on the fly
-- (Note that this is NOT a part of the solution.)
SELECT Parent_Item = CAST(d.Parent AS VARCHAR(20)),
Child_Item = CAST(d.Child AS VARCHAR(20))
INTO #dmBom
FROM (
SELECT '1', '2' UNION ALL
SELECT '1', '3' UNION ALL
SELECT '1', '4' UNION ALL
SELECT '1', '5' UNION ALL
SELECT '1', '6' UNION ALL
SELECT '1', '7' UNION ALL
SELECT '2', 'a' UNION ALL
SELECT '2', 'b' UNION ALL
SELECT '2', 'c' UNION ALL
SELECT '3', '12' UNION ALL
SELECT '3', '13' UNION ALL
SELECT '3', '14' UNION ALL
SELECT '4', 'x' UNION ALL
SELECT '4', 'y' UNION ALL
SELECT '4', 'z' UNION ALL
SELECT '9', 'c' UNION ALL
SELECT '9', 'd' UNION ALL
SELECT '9', 'e'
) d (Parent, Child)
;
--=================================================================================================
-- Solve the problem. Produce the list of parts in the expected "drill down order".
-- This lists more columns than what the OP requires but they have been included for clarity.
--=================================================================================================
--===== Conditionally drop the working table to make reruns in SSMS easier.
IF OBJECT_ID('TempDB..#WorkingBom','U') IS NOT NULL DROP TABLE #WorkingBom;
--===== Expand the hierarchy in "drill down order".
WITH
ctePartExplosion AS
( --=== This "anchor" section finds only the top level parts and put's them into level 1
SELECT Child_Item = p.Parent_Item, Parent_Item = CAST(NULL AS VARCHAR(20)), AssemblyLevel = 1,
HierarchicalPath = CAST('\'+p.Parent_Item AS VARCHAR(4000))
FROM ( ----- Find all parent's who are not also children. This is the top level.
SELECT Parent_Item FROM #dmBom
EXCEPT
SELECT Child_Item FROM #dmBom
) p
UNION ALL
--===== This "recursive" part of our query steps through all levels until there are no more
SELECT rcsv.Child_Item, rcsv.Parent_Item, AssemblyLevel = cte.AssemblyLevel + 1,
HierarchicalPath = CAST(cte.HierarchicalPath + '\'+rcsv.Child_Item AS VARCHAR(4000))
FROM #dmBom rcsv
INNER JOIN ctePartExplosion cte ON rcsv.Parent_Item = cte.Child_Item
) --=== This simply displays things in the expected "drill down order".
SELECT Child_Item = SPACE((AssemblyLevel-1)*2)+Child_Item, Parent_Item, AssemblyLevel, HierarchicalPath
FROM ctePartExplosion
ORDER BY HierarchicalPath
;
--Jeff Moden
Change is inevitable... Change for the better is not.