June 6, 2015 at 2:30 pm
Dear SQL Experts,
I have been trying to query SQL to generate relationship table for a set of files (CAD Files).They are stored in our Data management System.
I have attached a Picture of the file structure.
So farm I am able to get the following results from SQL.
DECLARE@Files TABLE( LEVEL VARCHAR(50), PARENT Varchar(50), CHILD Varchar(50))
INSERT@Files
SELECT'0' LEVEL, NULL PARENT, 'A' CHILD UNION ALL
SELECT'1' , 'A' , 'B' UNION ALL
SELECT'1' , 'A' , 'C' UNION ALL
SELECT'1' , 'A' , 'D' UNION ALL
SELECT'1' , 'A' , 'E' UNION ALL
SELECT'1' , 'A' , 'H' UNION ALL
SELECT'2' , 'E' , 'G' UNION ALL
SELECT'2' , 'E' , 'F' UNION ALL
SELECT'2' , 'H' , 'M' UNION ALL
SELECT'2' , 'H' , 'J' UNION ALL
SELECT'2' , 'H' , 'I' UNION ALL
SELECT'2' , 'J' , 'L' UNION ALL
SELECT'3' , 'J' , 'K'
select * from @Files
The problem with above result is that it take time to understand it.
How can get the following results from the Above data.
DECLARE@Files TABLE( ITEM_NO VARCHAR(50), PART_NAME Varchar(50) )
INSERT@Files
SELECT'0' ITEM_NO, 'A' PART_NAME UNION ALL
SELECT'1' , 'B' UNION ALL
SELECT'2' , 'C' UNION ALL
SELECT'3' , 'D' UNION ALL
SELECT'4' , 'E' UNION ALL
SELECT'4.1' , 'F' UNION ALL
SELECT'4.2' , 'G' UNION ALL
SELECT'5' , 'H' UNION ALL
SELECT'5.1' , 'I' UNION ALL
SELECT'5.2' , 'J' UNION ALL
SELECT'5.2.1' , 'K' UNION ALL
SELECT'5.2.2' , 'L' UNION ALL
SELECT'5.3' , 'M'
select * from @Files
Thanks and Regards
Ravi T
June 6, 2015 at 11:44 pm
There may be more elegant ways to do this, but it's late so I'm doing this the first way that occurs to me 🙂
The following should get you the desired results with the provided data:
DECLARE@Files TABLE( LEVEL VARCHAR(50), PARENT Varchar(50), CHILD Varchar(50))
INSERT@Files
SELECT'0' LEVEL, NULL PARENT, 'A' CHILD UNION ALL
SELECT'1' , 'A' , 'B' UNION ALL
SELECT'1' , 'A' , 'C' UNION ALL
SELECT'1' , 'A' , 'D' UNION ALL
SELECT'1' , 'A' , 'E' UNION ALL
SELECT'1' , 'A' , 'H' UNION ALL
SELECT'2' , 'E' , 'G' UNION ALL
SELECT'2' , 'E' , 'F' UNION ALL
SELECT'2' , 'H' , 'M' UNION ALL
SELECT'2' , 'H' , 'J' UNION ALL
SELECT'2' , 'H' , 'I' UNION ALL
SELECT'2' , 'J' , 'L' UNION ALL
SELECT'3' , 'J' , 'K' ;
WITH rCTE AS (
SELECT
Item_NO=CAST(ROw_NUMBER() OVER (ORDER BY Child ASC)-1 as varchar(max)),
PART_NAME=Child
FROM @Files
WHERE LEVEL<=1
UNION ALL
SELECT
Item_NO=P.Item_NO+'.'+CAST(ROw_NUMBER() OVER (ORDER BY Child ASC) as varchar(max)),
PART_NAME=C.Child
FROM rCTE P
INNER JOIN @Files C
ON P.PART_NAME=C.PARENT
WHERE C.LEVEL>1)
SELECT * FROM rCTE
ORDER BY Item_NO ASC
I hope this helps.
Cheers!
June 8, 2015 at 1:55 am
Thanks Jacob,It worked like charm.
Regards
Ravi T
July 2, 2015 at 12:12 pm
Hi All ,
I got answer to my query but ran into another problem.
I am not able to Add rCTE to a Temp Table.
and Every Part has a DocID how do i fetch it .
Regards
Ravi T
July 2, 2015 at 12:49 pm
santa326 (7/2/2015)
Hi All ,I got answer to my query but ran into another problem.
I am not able to Add rCTE to a Temp Table.
and Every Part has a DocID how do i fetch it .
Regards
Ravi T
Getting the data into a temp table is easy. The last part of the provided query should just be:
SELECT *
INTO #TempTableNameGoesHere
FROM rCTE
ORDER BY Item_NO ASC
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 3, 2015 at 8:19 am
Hi,
I cant get this it work.I have created over 7-8 temp table in the query.Everything works fine exxcept Putting CTE Data into Final Temp Table.
Regards
Ravi T
July 3, 2015 at 12:29 pm
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
July 5, 2015 at 2:46 pm
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)
July 20, 2015 at 12:59 pm
Hi,
I donno what I am missing.But this one task is proving to be the most challenging for me.
Sorry for the lack of experiance on CTE.
Can anyone please review the following code and give a solution or point me in the right direction ?
IF OBJECT_ID('tempdb..#TableA0') IS NOT NULL
DROP TABLE #Arihanth0
IF OBJECT_ID('tempdb..#Arihanth0a') IS NOT NULL
DROP TABLE #Arihanth0a
CREATE TABLE #TableA0 (
"Level" INT
--,DocumentID INT
,PARENT NVARCHAR(255)
,CHILD NVARCHAR(255)
)
--Select * from #Arihanth0
INSERT INTO #TableA0(
"Level"
,PARENT
,CHILD
)
SELECT'0' LEVEL, NULL PARENT, 'A' CHILD UNION ALL
SELECT'1' , 'A' , 'B' UNION ALL
SELECT'1' , 'A' , 'C' UNION ALL
SELECT'1' , 'A' , 'D' UNION ALL
SELECT'1' , 'A' , 'E' UNION ALL
SELECT'1' , 'A' , 'H' UNION ALL
SELECT'2' , 'E' , 'G' UNION ALL
SELECT'2' , 'E' , 'F' UNION ALL
SELECT'2' , 'H' , 'M' UNION ALL
SELECT'2' , 'H' , 'J' UNION ALL
SELECT'2' , 'H' , 'I' UNION ALL
SELECT'2' , 'J' , 'L' UNION ALL
SELECT'3' , 'J' , 'K'
select * from #TableA0
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
Regards
Ravi T
July 21, 2015 at 11:13 am
santa326 (7/20/2015)
Hi,I donno what I am missing.But this one task is proving to be the most challenging for me.
Sorry for the lack of experiance on CTE.
Can anyone please review the following code and give a solution or point me in the right direction ?
IF OBJECT_ID('tempdb..#TableA0') IS NOT NULL
DROP TABLE #Arihanth0
IF OBJECT_ID('tempdb..#Arihanth0a') IS NOT NULL
DROP TABLE #Arihanth0a
CREATE TABLE #TableA0 (
"Level" INT
--,DocumentID INT
,PARENT NVARCHAR(255)
,CHILD NVARCHAR(255)
)
--Select * from #Arihanth0
INSERT INTO #TableA0(
"Level"
,PARENT
,CHILD
)
SELECT'0' LEVEL, NULL PARENT, 'A' CHILD UNION ALL
SELECT'1' , 'A' , 'B' UNION ALL
SELECT'1' , 'A' , 'C' UNION ALL
SELECT'1' , 'A' , 'D' UNION ALL
SELECT'1' , 'A' , 'E' UNION ALL
SELECT'1' , 'A' , 'H' UNION ALL
SELECT'2' , 'E' , 'G' UNION ALL
SELECT'2' , 'E' , 'F' UNION ALL
SELECT'2' , 'H' , 'M' UNION ALL
SELECT'2' , 'H' , 'J' UNION ALL
SELECT'2' , 'H' , 'I' UNION ALL
SELECT'2' , 'J' , 'L' UNION ALL
SELECT'3' , 'J' , 'K'
select * from #TableA0
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
Regards
Ravi T
I can't be sure, but here's my attempt. I had to clean up your code quite a bit to get it to even work. You have to be sure your data types are going to match in your recursion efforts. Let me know:
CREATE TABLE #TableA0 (
[Level] INT
,PARENT NVARCHAR(255)
,CHILD NVARCHAR(255)
);
INSERT INTO #TableA0 ([Level], PARENT, CHILD)
SELECT'0' AS [Level], NULL AS PARENT, 'A' AS CHILD UNION ALL
SELECT'1' , 'A' , 'B' UNION ALL
SELECT'1' , 'A' , 'C' UNION ALL
SELECT'1' , 'A' , 'D' UNION ALL
SELECT'1' , 'A' , 'E' UNION ALL
SELECT'1' , 'A' , 'H' UNION ALL
SELECT'2' , 'E' , 'F' UNION ALL
SELECT'2' , 'E' , 'G' UNION ALL
SELECT'2' , 'H' , 'I' UNION ALL
SELECT'2' , 'H' , 'J' UNION ALL
SELECT'2' , 'H' , 'M' UNION ALL
SELECT'3' , 'J' , 'K' UNION ALL
SELECT'3' , 'J' , 'L'
SELECT *
FROM #TableA0
ORDER BY [Level], PARENT, CHILD
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 PARENT ASC) - 1 AS varchar(max)),
PART_NAME = CAST('' AS varchar(max)),
DOCID = CHILD
FROM #TableA0
WHERE [Level] = 0
UNION ALL
SELECT
Item_NO = P.Item_NO + '.' + CAST(ROW_NUMBER() OVER (ORDER BY C.PARENT ASC) AS varchar(max)),
PART_NAME = CAST(C.PARENT AS varchar(max)),
C.CHILD AS DOCID
FROM rCTE AS P
INNER JOIN #TableA0 C
ON P.DOCID = C.PARENT
WHERE C.[Level] > 0
)
INSERT INTO #TableA8(Item_No, PART_NAME, DOCID)
SELECT Item_NO, PART_NAME, DOCID
FROM rCTE
OPTION (MAXRECURSION 1000)
SELECT TA.*
FROM #TableA8 AS TA
ORDER BY TA.Item_No
DROP TABLE #TableA0
DROP TABLE #TableA8
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply