santa326 (7/3/2015)
Hi ,This is What I tried.
Finally I am trying to get Document ID as well into the Final Table.
CTE work fine if I execute from "WITH" till end.
--Indent Numbering
--select * from #TableA0
-- LevelDocumentIDParentNamePart
--0141030675.SLDASM
--161030675.SLDASMCAD800.SLDPRT
--1121030675.SLDASMAAD473.SLDPRT
--1131030675.SLDASM1030675_SA1.SLDASM
--1151030675.SLDASMWRSH7x38_1.75.SLDPRT
--1161030675.SLDASMWRCU30_25.SLDPRT
--1171030675.SLDASMSHHI0.8_9X900.SLDPRT
--1181030675.SLDASMSHFE3.2_19x94.SLDPRT
--1191030675.SLDASMSHAL1.6_10X305.SLDPRT
--1201030675.SLDASMRRFE10_18.SLDPRT
--1211030675.SLDASMNAB911.SLDPRT
--1221030675.SLDASMNAA240.SLDPRT
--1241030675.SLDASMFWBK14x132_0.5.SLDPRT
--1271030675.SLDASMDAA051_BKRX_AB.SLDPRT
--1281030675.SLDASMDAA050_GR_NL.SLDPRT
--1291030675.SLDASMDAA049_BL_AB_B.SLDPRT
--1301030675.SLDASMDAA049_BL_AB_A.SLDPRT
--1331030675.SLDASMCHAL6x21_1000.SLDPRT
--1341030675.SLDASMCAG860.SLDPRT
--1351030675.SLDASMCAF701_DAA055_BKRX.SLDASM
--1371030675.SLDASMCAF640.SLDPRT
--1381030675.SLDASMCAD830.SLDPRT
--271030675_SA1.SLDASMCAD071.SLDPRT
--281030675_SA1.SLDASMCAA862.SLDPRT
--291030675_SA1.SLDASMBAG840.SLDPRT
--2101030675_SA1.SLDASMBAD282.SLDPRT
--2111030675_SA1.SLDASMBAD281.SLDPRT
--2231030675_SA1.SLDASMFWRD1x26_0.12.SLDPRT
--2251030675_SA1.SLDASMFWBK1x26_0.12.SLDPRT
--2311030675_SA1.SLDASMDAA048_BL_AB_B.SLDPRT
--2321030675_SA1.SLDASMDAA048_BL_AB_A.SLDPRT
--239SHFE3.2_19x94.SLDPRTCut-List-Item1
--240SHAL1.6_10X305.SLDPRTCut-List-Item1
--226CAF701_DAA055_BKRX.SLDASMDAA055_BKRX_AB.SLDPRT
--236CAF701_DAA055_BKRX.SLDASMCAF701.SLDPRT
--Above is the content of TableA0
CREATE TABLE #TableA8(
Item_No varchar(max)
,PART_NAME varchar(max)
)
INSERT INTO #TableA8(
Item_No
,PART_NAME
);
WITH rCTE AS (
SELECT
Item_NO=CAST(ROw_NUMBER() OVER (ORDER BY Part ASC)-1 as varchar(max)),
PART_NAME=Part
--DOCID = DocumentID
FROM #TableA0
WHERE LEVEL<=1
UNION ALL
SELECT
Item_NO=P.Item_NO+'.'+CAST(ROw_NUMBER() OVER (ORDER BY Part ASC) as varchar(max)),
PART_NAME=C.Part
FROM rCTE P
INNER JOIN #TableA0 C
ON P.PART_NAME=C.ParentName
WHERE C.LEVEL>1)
SELECT Item_NO , PART_NAME FROM rCTE
ORDER BY Item_NO ASC
Thanks & Regards
Ravi T
You weren't all the far from the solution. It's just the order of things. I had previously provided you the needed changes to the last part of your previous query, but as the written word often doesn't do well across language differences, it may not have come across in a useful way. Try this:
CREATE TABLE #TableA8 (
Item_No varchar(max),
PART_NAME varchar(max),
DOCID varchar(20)
)
WITH rCTE AS (
SELECT
Item_NO=CAST(ROw_NUMBER() OVER (ORDER BY Part ASC)-1 as varchar(max)),
PART_NAME=Part,
DOCID = DocumentID
FROM #TableA0
WHERE LEVEL <= 1
UNION ALL
SELECT
Item_NO=P.Item_NO+'.'+CAST(ROw_NUMBER() OVER (ORDER BY Part ASC) as varchar(max)),
PART_NAME=C.Part,
C.DOCID
FROM rCTE P
INNER JOIN #TableA0 C
ON P.PART_NAME=C.ParentName
WHERE C.LEVEL > 1
)
INSERT INTO #TableA8(Item_No,PART_NAME,DOCID)
SELECT Item_NO, PART_NAME, DOCID
FROM rCTE
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)