How to Show a Part Map in SQL Server Using Recursion

10-September-2021
A very common problem in querying ERP or manufacturing MRP systems is that they use “self-join” tables to store their part information. These tables store “bill of materials” or “BOM” information, where complete assemblies of items can be found. However, it is often not so easy to easily show where a particular part “lives”. Is it 5 levels deep in the tree? 10 levels? What is the parent’s parent? What is the top level part? In this episode I will show you how to query this information using recursion, so that you can see the entire list of parts for a selected child part. Then, we’re going to pivot this hierarchy so that it can be inserted into a table which can then be viewed using tools like Power BI, which can create hierarchies on the fields we created, resulting in visuals with lots of depth and information.



Data Engineering Project? Contact me today!