My post on indenting output from hierarchical queries showed how to do a parts explosion on a Bill of Materials (BOM) that was implemented with an adjacency list pattern. One very simple change converts the query from a parts explosion to a where used query. The Sort column is shown so you can see how the query does its job. There's a lot of output. Highlighting was added to the output to help you understand the groupings as the hierarchyid is walked upwards.
;WITH BOMcte(ComponentID, Name, PerAssemblyQty, ComputedLevel, ProductAssemblyID, Sort)
AS
(
SELECT b.ComponentID,
CAST(p.Name as nvarchar(100)),
b.PerAssemblyQty,
0,
b.ProductAssemblyID,
CAST('\' + p.Name as nvarchar(254))
FROM Production.BillOfMaterials AS b
INNER JOIN Production.Product p
on b.ComponentID = p.ProductID
WHERE b.EndDate IS NULL -- only retrieve components still being used
and b.ComponentID = 523 -- specify a component to find where it is used
UNION ALL
SELECT b.ComponentID,
CAST(REPLICATE ('| ' , ComputedLevel) + p.Name as nvarchar(100)),
b.PerAssemblyQty,
ComputedLevel + 1,
b.ProductAssemblyID,
CAST(cte.Sort + '\' + p.Name as nvarchar(254))
FROM Production.BillOfMaterials as b
INNER JOIN Production.Product p
on b.ComponentID = p.ProductID
INNER JOIN BOMcte AS cte
ON b.ComponentID = cte.ProductAssemblyID --b.ProductAssemblyID = cte.ComponentID in parts explosion
WHERE b.EndDate IS NULL -- only retrieve components still being used
)
SELECT ProductAssemblyID, ComponentID, Name, PerAssemblyQty, Sort
FROM BOMcte
ORDER BY Sort;
ProductAssemblyID
| ComponentID PerAssemblyQty
| | Name | Sort
---- --- ----------------------------------- ---- ------------------------------------------------------------------------
400 523 LL Spindle/Axle 1.00 \LL Spindle/Axle
815 400 LL Hub 1.00 \LL Spindle/Axle\LL Hub
818 400 LL Hub 1.00 \LL Spindle/Axle\LL Hub
823 400 LL Hub 1.00 \LL Spindle/Axle\LL Hub
826 400 LL Hub 1.00 \LL Spindle/Axle\LL Hub
980 815 | LL Mountain Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel
981 815 | LL Mountain Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel
982 815 | LL Mountain Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel
983 815 | LL Mountain Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel
984 815 | LL Mountain Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel
985 815 | LL Mountain Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel
986 815 | LL Mountain Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel
987 815 | LL Mountain Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel
988 815 | LL Mountain Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel
989 815 | LL Mountain Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel
990 815 | LL Mountain Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel
991 815 | LL Mountain Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel
992 815 | LL Mountain Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel
993 815 | LL Mountain Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel
NULL 980 | | Mountain-400-W Silver, 38 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-400-W Silver, 38
NULL 981 | | Mountain-400-W Silver, 40 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-400-W Silver, 40
NULL 982 | | Mountain-400-W Silver, 42 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-400-W Silver, 42
NULL 983 | | Mountain-400-W Silver, 46 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-400-W Silver, 46
NULL 989 | | Mountain-500 Black, 40 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Black, 40
NULL 990 | | Mountain-500 Black, 42 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Black, 42
NULL 991 | | Mountain-500 Black, 44 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Black, 44
NULL 992 | | Mountain-500 Black, 48 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Black, 48
NULL 993 | | Mountain-500 Black, 52 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Black, 52
NULL 984 | | Mountain-500 Silver, 40 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Silver, 40
NULL 985 | | Mountain-500 Silver, 42 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Silver, 42
NULL 986 | | Mountain-500 Silver, 44 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Silver, 44
NULL 987 | | Mountain-500 Silver, 48 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Silver, 48
NULL 988 | | Mountain-500 Silver, 52 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Silver, 52
980 823 | LL Mountain Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel
981 823 | LL Mountain Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel
982 823 | LL Mountain Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel
983 823 | LL Mountain Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel
984 823 | LL Mountain Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel
985 823 | LL Mountain Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel
986 823 | LL Mountain Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel
987 823 | LL Mountain Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel
988 823 | LL Mountain Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel
989 823 | LL Mountain Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel
990 823 | LL Mountain Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel
991 823 | LL Mountain Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel
992 823 | LL Mountain Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel
993 823 | LL Mountain Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel
NULL 980 | | Mountain-400-W Silver, 38 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-400-W Silver, 38
NULL 981 | | Mountain-400-W Silver, 40 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-400-W Silver, 40
NULL 982 | | Mountain-400-W Silver, 42 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-400-W Silver, 42
NULL 983 | | Mountain-400-W Silver, 46 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-400-W Silver, 46
NULL 989 | | Mountain-500 Black, 40 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Black, 40
NULL 990 | | Mountain-500 Black, 42 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Black, 42
NULL 991 | | Mountain-500 Black, 44 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Black, 44
NULL 992 | | Mountain-500 Black, 48 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Black, 48
NULL 993 | | Mountain-500 Black, 52 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Black, 52
NULL 984 | | Mountain-500 Silver, 40 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Silver, 40
NULL 985 | | Mountain-500 Silver, 42 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Silver, 42
NULL 986 | | Mountain-500 Silver, 44 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Silver, 44
NULL 987 | | Mountain-500 Silver, 48 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Silver, 48
NULL 988 | | Mountain-500 Silver, 52 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Silver, 52
759 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
760 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
761 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
762 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
763 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
764 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
765 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
766 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
767 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
768 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
769 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
770 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
977 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
997 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
998 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
999 818 | LL Road Front Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel
NULL 768 | | Road-650 Black, 44 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Black, 44
NULL 769 | | Road-650 Black, 48 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Black, 48
NULL 770 | | Road-650 Black, 52 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Black, 52
NULL 765 | | Road-650 Black, 58 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Black, 58
NULL 766 | | Road-650 Black, 60 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Black, 60
NULL 767 | | Road-650 Black, 62 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Black, 62
NULL 762 | | Road-650 Red, 44 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Red, 44
NULL 763 | | Road-650 Red, 48 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Red, 48
NULL 764 | | Road-650 Red, 52 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Red, 52
NULL 759 | | Road-650 Red, 58 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Red, 58
NULL 760 | | Road-650 Red, 60 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Red, 60
NULL 761 | | Road-650 Red, 62 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Red, 62
NULL 997 | | Road-750 Black, 44 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-750 Black, 44
NULL 998 | | Road-750 Black, 48 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-750 Black, 48
NULL 999 | | Road-750 Black, 52 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-750 Black, 52
NULL 977 | | Road-750 Black, 58 1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-750 Black, 58
759 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
760 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
761 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
762 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
763 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
764 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
765 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
766 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
767 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
768 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
769 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
770 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
977 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
997 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
998 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
999 826 | LL Road Rear Wheel 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel
NULL 768 | | Road-650 Black, 44 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Black, 44
NULL 769 | | Road-650 Black, 48 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Black, 48
NULL 770 | | Road-650 Black, 52 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Black, 52
NULL 765 | | Road-650 Black, 58 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Black, 58
NULL 766 | | Road-650 Black, 60 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Black, 60
NULL 767 | | Road-650 Black, 62 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Black, 62
NULL 762 | | Road-650 Red, 44 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Red, 44
NULL 763 | | Road-650 Red, 48 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Red, 48
NULL 764 | | Road-650 Red, 52 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Red, 52
NULL 759 | | Road-650 Red, 58 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Red, 58
NULL 760 | | Road-650 Red, 60 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Red, 60
NULL 761 | | Road-650 Red, 62 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Red, 62
NULL 997 | | Road-750 Black, 44 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-750 Black, 44
NULL 998 | | Road-750 Black, 48 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-750 Black, 48
NULL 999 | | Road-750 Black, 52 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-750 Black, 52
NULL 977 | | Road-750 Black, 58 1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-750 Black, 58
The quantity of the results can easily be a bit overwhelming in a where used query, particularly if the component (i.e., part) is a very common and standard sized item such as a bolt or nut. You may want to restrict the output to only show the top level assemblies that use a particular part as shown in this query:
;WITH BOMcte(ComponentID, Name, PerAssemblyQty, ComputedLevel, ProductAssemblyID, Sort)
AS
(
SELECT b.ComponentID,
CAST(p.Name as nvarchar(100)),
b.PerAssemblyQty,
0,
b.ProductAssemblyID,
CAST('\' + p.Name as nvarchar(254))
FROM Production.BillOfMaterials AS b
INNER JOIN Production.Product p
on b.ComponentID = p.ProductID
WHERE b.EndDate IS NULL -- only retrieve components still being used
and b.ComponentID = 523 -- specify a component to find where it is used
UNION ALL
SELECT b.ComponentID,
CAST(REPLICATE ('| ' , ComputedLevel) + p.Name as nvarchar(100)),
b.PerAssemblyQty,
ComputedLevel + 1,
b.ProductAssemblyID,
CAST(cte.Sort + '\' + p.Name as nvarchar(254))
FROM Production.BillOfMaterials as b
INNER JOIN Production.Product p
on b.ComponentID = p.ProductID
INNER JOIN BOMcte AS cte
ON b.ComponentID = cte.ProductAssemblyID --b.ProductAssemblyID = cte.ComponentID in parts explosion
WHERE b.EndDate IS NULL -- only retrieve components still being used
)
SELECT ComponentID, Name
FROM BOMcte
WHERE ProductAssemblyID is NULL -- only show the top level assemblies
ORDER BY Sort;
ComponentID Name
----------- -----------------------------------
980 | | Mountain-400-W Silver, 38
981 | | Mountain-400-W Silver, 40
982 | | Mountain-400-W Silver, 42
983 | | Mountain-400-W Silver, 46
989 | | Mountain-500 Black, 40
990 | | Mountain-500 Black, 42
991 | | Mountain-500 Black, 44
992 | | Mountain-500 Black, 48
993 | | Mountain-500 Black, 52
984 | | Mountain-500 Silver, 40
985 | | Mountain-500 Silver, 42
986 | | Mountain-500 Silver, 44
987 | | Mountain-500 Silver, 48
988 | | Mountain-500 Silver, 52
980 | | Mountain-400-W Silver, 38
981 | | Mountain-400-W Silver, 40
982 | | Mountain-400-W Silver, 42
983 | | Mountain-400-W Silver, 46
989 | | Mountain-500 Black, 40
990 | | Mountain-500 Black, 42
991 | | Mountain-500 Black, 44
992 | | Mountain-500 Black, 48
993 | | Mountain-500 Black, 52
984 | | Mountain-500 Silver, 40
985 | | Mountain-500 Silver, 42
986 | | Mountain-500 Silver, 44
987 | | Mountain-500 Silver, 48
988 | | Mountain-500 Silver, 52
768 | | Road-650 Black, 44
769 | | Road-650 Black, 48
770 | | Road-650 Black, 52
765 | | Road-650 Black, 58
766 | | Road-650 Black, 60
767 | | Road-650 Black, 62
762 | | Road-650 Red, 44
763 | | Road-650 Red, 48
764 | | Road-650 Red, 52
759 | | Road-650 Red, 58
760 | | Road-650 Red, 60
761 | | Road-650 Red, 62
997 | | Road-750 Black, 44
998 | | Road-750 Black, 48
999 | | Road-750 Black, 52
977 | | Road-750 Black, 58
768 | | Road-650 Black, 44
769 | | Road-650 Black, 48
770 | | Road-650 Black, 52
765 | | Road-650 Black, 58
766 | | Road-650 Black, 60
767 | | Road-650 Black, 62
762 | | Road-650 Red, 44
763 | | Road-650 Red, 48
764 | | Road-650 Red, 52
759 | | Road-650 Red, 58
760 | | Road-650 Red, 60
761 | | Road-650 Red, 62
997 | | Road-750 Black, 44
998 | | Road-750 Black, 48
999 | | Road-750 Black, 52
977 | | Road-750 Black, 58
The logic from the parts explosion for indenting the output is no longer relevant, so you can remove it from the output to clean up the results.
;WITH BOMcte(ComponentID, Name, PerAssemblyQty, ComputedLevel, ProductAssemblyID, Sort)
AS
(
SELECT b.ComponentID,
p.Name,
b.PerAssemblyQty,
0,
b.ProductAssemblyID,
CAST('\' + p.Name as nvarchar(254))
FROM Production.BillOfMaterials AS b
INNER JOIN Production.Product p
on b.ComponentID = p.ProductID
WHERE b.EndDate IS NULL -- only retrieve components still being used
and b.ComponentID = 523 -- specify a component to find where it is used
UNION ALL
SELECT b.ComponentID,
p.Name,
b.PerAssemblyQty,
ComputedLevel + 1,
b.ProductAssemblyID,
CAST(cte.Sort + '\' + p.Name as nvarchar(254))
FROM Production.BillOfMaterials as b
INNER JOIN Production.Product p
on b.ComponentID = p.ProductID
INNER JOIN BOMcte AS cte
ON b.ComponentID = cte.ProductAssemblyID --b.ProductAssemblyID = cte.ComponentID in parts explosion
WHERE b.EndDate IS NULL -- only retrieve components still being used
)
SELECT ComponentID, Name
FROM BOMcte
WHERE ProductAssemblyID is NULL -- only show the top level assemblies
ORDER BY Sort;
to get this (output truncated for brevity):
ComponentID Name
---------- -------------------------
980 Mountain-400-W Silver, 38
981 Mountain-400-W Silver, 40
982 Mountain-400-W Silver, 42
983 Mountain-400-W Silver, 46
989 Mountain-500 Black, 40