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