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

Need recursive CTE to extract BOM Model/Option columns with structure for lower level items Expand / Collapse
Author
Message
Posted Wednesday, November 13, 2013 5:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 3:19 PM
Points: 4, 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')

Post #1514114
Posted Wednesday, November 13, 2013 6:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:58 PM
Points: 3,422, Visits: 5,366
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!
Post #1514122
Posted Thursday, November 14, 2013 9:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 3:19 PM
Points: 4, 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.

Post #1514367
Posted Thursday, November 14, 2013 5:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:58 PM
Points: 3,422, Visits: 5,366
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!
Post #1514536
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse