SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need recursive CTE to extract BOM Model/Option columns with structure for lower level items


Need recursive CTE to extract BOM Model/Option columns with structure for lower level items

Author
Message
labenroth
labenroth
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 29
What I Need....

I need to extract a partially "de-normalized" data set from an indented BOM in a MSSS 2008R2 environment. I have some experience with recursive CTE's, but in this case I am at a loss as to how I can get the results I need.

I start with the data table below.

The LEVEL 0 items represent a configured model and LEVEL 1 items represent the various option types. LEVEL 2 items are the available options. Lower level items may be additional phantom items (PH) or they may be purchased (PUR1, PUR2, PUR3)

Table: SAMPLE_BOM (see bottom of page for code to generate table data)

DATA TABLE:

LEVEL LEVEL_INDENTED COMPONENT_ITEM PARENT_ITEM ITEM_TYPE
---- -------------- -------------- ----------- ---------
0 0 MODEL-001 NULL ATO
1 _1 OPTN-0290 MODEL-001 PHATO
2 __2 844 OPTN-0290 PH
3 ___3 864 844 PH
4 ____4 410 864 PH
5 _____5 358 410 PUR1
5 _____5 868 410 PUR2
5 _____5 147 410 PUR1
4 ____4 409 864 PH
5 _____5 494 409 PUR2
5 _____5 139 409 PUR1
5 _____5 619 409 PUR1
4 ____4 125 864 PH
5 _____5 028 125 PUR1
1 _1 OPTN-0291 MODEL-001 PHATO
2 __2 634 OPTN-0291 PH
3 ___3 497 634 PH
4 ____4 052 497 PUR1
4 ____4 055 497 PUR3
4 ____4 095 497 PUR3
4 ____4 071 497 PUR1
3 ___3 754 634 PH
4 ____4 139 754 PUR1
3 ___3 003 634 PH
4 ____4 002 003 PUR1
4 ____4 494 003 PUR2
1 _1 OPTN-0347 MODEL-001 PHATO
2 __2 063 OPTN-0347 PH
3 ___3 064 063 PH
4 ____4 058 064 PUR1
5 _____5 437 058 PUR2
5 _____5 055 058 PUR3
5 _____5 095 058 PUR3
5 _____5 424 058 UR1




I want to generate the desired result shown below. It is to include separate columns for the Model and Option while also maintaining the BOM structure for the lower levels.

How do I create a CTE that will generate the Model and Option columns?

Desired result:


MODEL OPTION LEVEL LEVEL_INDENTED COMPONENT_ITEM PARENT_ITEM ITEM_TYPE
--------- --------- ---- -------------- -------------- ----------- ---------
MODEL-001 OPTN-0290 2 __2 844 OPTN-0290 PH
MODEL-001 OPTN-0290 3 ___3 864 844 PH
MODEL-001 OPTN-0290 4 ____4 410 864 PH
MODEL-001 OPTN-0290 5 _____5 358 410 PUR1
MODEL-001 OPTN-0290 5 _____5 147 410 PUR1
MODEL-001 OPTN-0290 4 ____4 409 864 PH
MODEL-001 OPTN-0290 5 _____5 139 409 PUR1
MODEL-001 OPTN-0290 5 _____5 619 409 PUR1
MODEL-001 OPTN-0290 4 ____4 125 864 PH
MODEL-001 OPTN-0290 5 _____5 028 125 PUR1
MODEL-001 OPTN-0291 2 __2 634 OPTN-0291 PH
MODEL-001 OPTN-0291 3 ___3 497 634 PH
MODEL-001 OPTN-0291 4 ____4 052 497 PUR1
MODEL-001 OPTN-0291 4 ____4 071 497 PUR1
MODEL-001 OPTN-0291 3 ___3 754 634 PH
MODEL-001 OPTN-0291 4 ____4 139 754 PUR1
MODEL-001 OPTN-0291 3 ___3 003 634 PH
MODEL-001 OPTN-0291 4 ____4 002 003 PUR1
MODEL-001 OPTN-0347 2 __2 063 OPTN-0347 PH
MODEL-001 OPTN-0347 3 ___3 064 063 PH
MODEL-001 OPTN-0347 4 ____4 058 064 PUR1
MODEL-001 OPTN-0347 5 _____5 424 058 PUR1



My attempt at a CTE thus far is shown below ....

WITH ANCHR --Anchor member definition
AS
(
SELECT
AQ.LEVEL
, AQ.LEVEL_INDENTED
, AQ.COMPONENT_ITEM
, AQ.PARENT_ITEM
, AQ.ITEM_TYPE
FROM
(
SELECT
LEVEL
, LEVEL_INDENTED
, COMPONENT_ITEM
, PARENT_ITEM
, ITEM_TYPE
FROM SAMPLE_BOM
) AQ
WHERE
AQ.ITEM_TYPE = 'PHATO'
UNION ALL
--Recursive member definition
SELECT
RQ.LEVEL
, RQ.LEVEL_INDENTED
, RQ.COMPONENT_ITEM
, RQ.PARENT_ITEM
, RQ.ITEM_TYPE
FROM
(
SELECT
LEVEL
, LEVEL_INDENTED
, COMPONENT_ITEM
, PARENT_ITEM
, ITEM_TYPE
FROM SAMPLE_BOM
) RQ
INNER JOIN ANCHR A0
ON
(
A0.COMPONENT_ITEM = RQ.PARENT_ITEM
)
)
--Executes CTE
SELECT
A1.LEVEL AS A1_LVL
, A1.LEVEL_INDENTED AS A1_LVL_INDENTED
, A1.COMPONENT_ITEM AS A1_ITEM
, A1.PARENT_ITEM AS A1_PARENT
, A1.ITEM_TYPE AS A1_TYPE
FROM ANCHR A1
WHERE A1.ITEM_TYPE <> 'PHATO'
OPTION (maxrecursion 10)



...which generates the following result.
It is partially correct but unfortunately lacks the MODEL and OPTION columns


LEVEL LEVEL_INDENTED COMPONENT_ITEM PARENT_ITEM ITEM_TYPE
----- -------------- -------------- ----------- ---------
2 __2 063 OPTN-0347 PH
3 ___3 064 063 PH
4 ____4 058 064 PUR1
5 _____5 437 058 PUR2
5 _____5 055 058 PUR3
5 _____5 095 058 PUR3
5 _____5 424 058 PUR1
2 __2 634 OPTN-0291 PH
3 ___3 497 634 PH
3 ___3 754 634 PH
3 ___3 003 634 PH
4 ____4 002 003 PUR1
4 ____4 494 003 PUR2
4 ____4 139 754 PUR1
4 ____4 052 497 PUR1
4 ____4 055 497 PUR3
4 ____4 095 497 PUR3
4 ____4 071 497 PUR1
2 __2 844 OPTN-0290 PH
3 ___3 864 844 PH
4 ____4 410 864 PH
4 ____4 409 864 PH
4 ____4 125 864 PH
5 _____5 028 125 PUR1
5 _____5 494 409 PUR2
5 _____5 139 409 PUR1
5 _____5 619 409 PUR1
5 _____5 358 410 PUR1
5 _____5 868 410 PUR2
5 _____5 147 410 PUR1



Code to generate the sample data:

CREATE TABLE SAMPLE_BOM(
LEVEL nchar(2) NULL,
LEVEL_INDENTED nchar(15) NULL,
COMPONENT_ITEM nchar(50) NULL,
PARENT_ITEM nchar(50) NULL,
ITEM_TYPE nchar(10) NULL) ON [PRIMARY]

INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('0','0','MODEL-001','NULL','ATO')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('1','_1','OPTN-0290','MODEL-001','PHATO')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('2','__2','844','OPTN-0290','PH')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('3','___3','864','844','PH')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('4','____4','410','864','PH')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('5','_____5','358','410','PUR1')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('5','_____5','868','410','PUR2')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('5','_____5','147','410','PUR1')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('4','____4','409','864','PH')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('5','_____5','494','409','PUR2')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('5','_____5','139','409','PUR1')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('5','_____5','619','409','PUR1')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('4','____4','125','864','PH')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('5','_____5','028','125','PUR1')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('1','_1','OPTN-0291','MODEL-001','PHATO')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('2','__2','634','OPTN-0291','PH')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('3','___3','497','634','PH')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('4','____4','052','497','PUR1')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('4','____4','055','497','PUR3')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('4','____4','095','497','PUR3')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('4','____4','071','497','PUR1')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('3','___3','754','634','PH')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('4','____4','139','754','PUR1')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('3','___3','003','634','PH')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('4','____4','002','003','PUR1')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('4','____4','494','003','PUR2')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('1','_1','OPTN-0347','MODEL-001','PHATO')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('2','__2','063','OPTN-0347','PH')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('3','___3','064','063','PH')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('4','____4','058','064','PUR1')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('5','_____5','437','058','PUR2')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('5','_____5','055','058','PUR3')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('5','_____5','095','058','PUR3')
INSERT INTO SAMPLE_BOM(LEVEL,LEVEL_INDENTED,COMPONENT_ITEM,PARENT_ITEM,ITEM_TYPE) VALUES('5','_____5','424','058','PUR1')


dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17351 Visits: 6431

WITH NewBOM AS
(
SELECT MODEL=COMPONENT_ITEM, [OPTION]=PARENT_ITEM
,[LEVEL], LEVEL_INDENTED, COMPONENT_ITEM, PARENT_ITEM, ITEM_TYPE
,[SORT]=CAST([LEVEL] AS VARCHAR(8000))
FROM SAMPLE_BOM
WHERE LEVEL = 0
UNION ALL
SELECT MODEL, [OPTION]=CASE b.[LEVEL] WHEN 2 THEN b.PARENT_ITEM ELSE [OPTION] END
,b.[LEVEL], b.LEVEL_INDENTED, b.COMPONENT_ITEM, b.PARENT_ITEM, b.ITEM_TYPE
,[SORT] + '/' + CAST(RTRIM(b.COMPONENT_ITEM) AS VARCHAR(8000))
FROM NewBOM a
JOIN SAMPLE_BOM b ON a.COMPONENT_ITEM = b.PARENT_ITEM
)
SELECT MODEL, [OPTION], [LEVEL], LEVEL_INDENTED, COMPONENT_ITEM, PARENT_ITEM, ITEM_TYPE, [SORT]
FROM NewBOM
WHERE [LEVEL] > 1
ORDER BY MODEL, [SORT];




You may need to play a bit with [SORT] to get the BOM displayed in exactly the order you want.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
labenroth
labenroth
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 29
You, sir, are officially "The Man"!

Thank you very much. Your solution works and I have learned something about CTE's to boot.

You also gave some ideas about to make use of the [SORT] concept using ROW_NUMBER() to maintain sort order when ambiguities arise in the concatenated string.
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17351 Visits: 6431
Glad to be of service!


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search