November 13, 2013 at 5:26 pm
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:
LEVELLEVEL_INDENTEDCOMPONENT_ITEMPARENT_ITEMITEM_TYPE
----------------------------------------------------
00MODEL-001NULLATO
1_1OPTN-0290MODEL-001PHATO
2__2844OPTN-0290PH
3___3864844PH
4____4410864PH
5_____5358410PUR1
5_____5868410PUR2
5_____5147410PUR1
4____4409864PH
5_____5494409PUR2
5_____5139409PUR1
5_____5619409PUR1
4____4125864PH
5_____5028125PUR1
1_1OPTN-0291MODEL-001PHATO
2__2634OPTN-0291PH
3___3497634PH
4____4052497PUR1
4____4055497PUR3
4____4095497PUR3
4____4071497PUR1
3___3754634PH
4____4139754PUR1
3___3003634PH
4____4002003PUR1
4____4494003PUR2
1_1OPTN-0347MODEL-001PHATO
2__2063OPTN-0347PH
3___3064063PH
4____4058064PUR1
5_____5437058PUR2
5_____5055058PUR3
5_____5095058PUR3
5_____5424058UR1
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')
November 13, 2013 at 6:05 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 14, 2013 at 9:55 am
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.
November 14, 2013 at 5:12 pm
Glad to be of service!
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply