Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

Recursive query for a Bill of Materials table Expand / Collapse
Author
Message
Posted Monday, April 11, 2011 3:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 36,749, Visits: 31,197
david.moule (4/11/2011)
Hi Jeff,

I got your .net email and have sent a test reply to it.....



I didn't get it as of 5:26 PM


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1091789
Posted Monday, April 11, 2011 3:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:59 AM
Points: 78, Visits: 410
I take it you didn't actually read what I sent you, then.

--Jeff Moden
--------------------------------------------------------------------------------


Yep of course I read it

But I haven't had a chance to convert my code to that method yet. Still work in progress. The seeding list of the top level BOM's seems to be the key. I've posted my test sample so that it is clear what I am aiming for, and where I am upto so far..... your signature block pointer was very helpful...

Post #1091795
Posted Friday, April 22, 2011 12:45 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:36 AM
Points: 1,565, Visits: 2,378
Jeff/Dave - I've actually used the BOL example as a template for several procedures since this post began, and I am very interested in learning of the solution to Dave's problem. Any way you guys could post the solution to the problem?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1097594
Posted Saturday, April 23, 2011 1:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 36,749, Visits: 31,197
Greg Snidow (4/22/2011)
Jeff/Dave - I've actually used the BOL example as a template for several procedures since this post began, and I am very interested in learning of the solution to Dave's problem. Any way you guys could post the solution to the problem?


Sure... as usual, comments in the code...
--===== 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, Parent_Item, AssemblyLevel, HierarchicalPath
FROM ctePartExplosion
ORDER BY HierarchicalPath
;




--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1097790
Posted Saturday, April 23, 2011 1:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 36,749, Visits: 31,197
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1097792
Posted Tuesday, June 21, 2011 7:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:59 AM
Points: 78, Visits: 410
Here's my code for the finished example where a list of Top Bills of Materials is used and a product structure table, to create a BOM explosion:

------------------------------------------------------------------------
-- BOM explosion example, using a seed list
-- Thanks to jhood for initial example, modified to explode a list of top bills of material for dm:

-- Drop temp tables if they exist
IF OBJECT_ID('TempDB..#dmBOM','U') IS NOT NULL
DROP TABLE #dmBOM
IF OBJECT_ID('TempDB..#dmBOM_expanded','U') IS NOT NULL
DROP TABLE #dmBOM_expanded
IF OBJECT_ID('TempDB..#dmBOM_final_output','U') IS NOT NULL
DROP TABLE #dmBOM_final_output

-- Input table is the product structure table
-- Desired output is a table containing all unique parent and child parts

-- Create the product structure table,
-- each top bill of material with all of their child components
CREATE TABLE #dmBOM (parent_item varchar(20), child_item varchar(20))

INSERT INTO #dmBOM VALUES ('1', '2')
INSERT INTO #dmBOM VALUES ('1', '3')
INSERT INTO #dmBOM VALUES ('1', '4')
INSERT INTO #dmBOM VALUES ('1', '5')
INSERT INTO #dmBOM VALUES ('1', '6')
INSERT INTO #dmBOM VALUES ('1', '7')
INSERT INTO #dmBOM VALUES ('2', 'a')
INSERT INTO #dmBOM VALUES ('2', 'b')
INSERT INTO #dmBOM VALUES ('2', 'c')
INSERT INTO #dmBOM VALUES ('3', '12')
INSERT INTO #dmBOM VALUES ('3', '13')
INSERT INTO #dmBOM VALUES ('3', '14')
INSERT INTO #dmBOM VALUES ('4', 'x')
INSERT INTO #dmBOM VALUES ('4', 'y')
INSERT INTO #dmBOM VALUES ('4', 'z')
INSERT INTO #dmBOM VALUES ('9', 'd')
INSERT INTO #dmBOM VALUES ('9', 'e')
INSERT INTO #dmBOM VALUES ('9', 'f')
INSERT INTO #dmBOM VALUES ('a', 'g')
INSERT INTO #dmBOM VALUES ('b', 'h')
INSERT INTO #dmBOM VALUES ('c', 'i')

-- Create the intermediate output table and initialise it with all the
-- top bills of material that need exploding
CREATE TABLE #dmBOM_expanded
(item varchar(20), i_level INT)

INSERT INTO #dmBOM_expanded (item, i_level)
SELECT distinct parent_item, 0 AS i_level
FROM #dmBOM

-- Create and set a level counter, then explode the hierarchy using the product
-- structure table, then remove duplicates to get the final output:
DECLARE @CurrentLevel INT
SET @CurrentLevel = 0
WHILE @@ROWCOUNT > 0
BEGIN
SET @CurrentLevel = @CurrentLevel + 1
INSERT INTO #dmBOM_expanded (item, i_level)
SELECT p.child_item as item, @CurrentLevel AS i_level
FROM #dmBOM as p
INNER JOIN #dmBOM_expanded as h
ON p.parent_item = h.item
AND h.i_level = @CurrentLevel - 1
END
select distinct item
into #dmBOM_final_output
from #dmBOM_expanded
order by item

select * from #dmBOM order by parent_item, child_item
select * from #dmBOM_expanded order by item, i_level
select * from #dmBOM_final_output order by item

-----------------------------------------------------------------------------------
-- Cleanup temporary tables when verified
IF OBJECT_ID('TempDB..#dmBOM','U') IS NOT NULL
DROP TABLE #dmBOM
IF OBJECT_ID('TempDB..#dmBOM_expanded','U') IS NOT NULL
DROP TABLE #dmBOM_expanded
IF OBJECT_ID('TempDB..#dmBOM_final_output','U') IS NOT NULL
DROP TABLE #dmBOM_final_output
-----------------------------------------------------------------------------------

Regards, Dave


Post #1129007
Posted Thursday, June 6, 2013 11:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 6, 2013 11:53 AM
Points: 2, Visits: 4
I have a working BOM recursive CTE, much like this one, but now I need to be able to input the part number I need the BOM from and have no clue how to do it.

If I put in a where clause right after the first part of the CTE with a specific part number it will give me the results I need but I need to be able to tell the database which part number I need the BOM from easily.

Any ideas on how to achieve this?
Post #1460832
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse