All geek questions in one place

TSQL - query expansion consisting of cross-application and rotation

This question is based on my previous question . I need to expand the query so that I can include two other tables (running on a different server instance).

In this Fiddle, I added these two tables:

CREATE TABLE LookUp ([docID] varchar(10), [docType] varchar(100), [PartNumber] varchar(100), [internalID] varchar(100)); INSERT INTO LookUp ([docID],[docType],[PartNumber], [internalID]) VALUES ('D0305415', 'docTypeSub', 'X0455', null), ('D0157632', 'docTypeMain', null, 'XY05570-XY05571'), ('D0181511', 'docTypeMain',null, 'XY05572-XY05573'), ('D0157633', 'docTypeMain', null, 'XY06380-XY06381'), ('D0156037', 'docTypeSub', 'X0326', null), ('D0151874', 'docTypeMain', null, 'XY05345'); CREATE TABLE Links ([docIDTop] varchar(10), [docIDBottom] varchar(10)); INSERT INTO Links ([docIDTop],[docIDBottom]) VALUES ('D0157632', 'D0305415'), ('D0181511', 'D0305415'), ('D0157633', 'D0305415'), ('D0151874', 'D0156037');

As for the output, I need to display the new internalID column in the comma delimited column based on the PartNumber column.

This is a query that prints the correct data:

  select c.docType AS c_docTypeSub, c.docID AS C_docID, c.PartNumber AS C_PartNumber , b.docIDTop AS B_docIdTop, b.docIDBottom AS B_docIdBottom, a.* FROM LookUp a, Links b, LookUp c WHERE a.docType = 'docTypeMain' and a.docID = b.docIDTop and b.docIDBottom = c.docID and c.docType = 'docTypeSub' ;

My problem is to collect these fragments so that I can show the InternalID in my old query below:

 ---------------- -- OLD Query -- ---------------- WITH CTE_no_nums AS ( SELECT docID, CASE WHEN PATINDEX('%[0-9]%',column1) > 0 THEN SUBSTRING(column1,0,PATINDEX('%[0-9]%',column1)) ELSE column1 END AS cols, COALESCE(column2,column3) AS vals FROM miscValues WHERE column2 IS NOT NULL OR column3 IS NOT NULL ), CTE_Pivot AS ( SELECT docID,partNumber,prio,[length],material FROM CTE_no_nums PIVOT ( MAX(vals) FOR cols IN (partNumber,prio,[length],material) ) pvt ) SELECT A.docId + ' # ' + B.vals AS [DocID # Plant], A.docID, A.partNumber, A.prio, B.vals AS Plant, A.partNumber + '#' + A.material + '#' + A.[length] AS Identification, A.[length], SUBSTRING(CA.colors,0,LEN(CA.colors)) colors --substring removes last comma FROM CTE_Pivot A INNER JOIN CTE_no_nums B ON A.docID = B.docID AND B.cols = 'Plant' CROSS APPLY ( SELECT vals + ',' FROM CTE_no_nums C WHERE cols = 'Color' AND C.docID = A.docID FOR XML PATH('') ) CA(colors) ;

I hope you can show me how this can be achieved. If something is unclear, feel free to ask. And no, I am not responsible for the data structure :-)

Thanks.

+5
source share
2 answers

Juan Ruiz de Castilla expanded my answer and opened my eyes to your “links”.

This is my final suggestion resolving your issue in another CTE:

 CREATE TABLE MiscValues ([docID] varchar(10) ,[rowNumber] int, [Column1] varchar(100), [Column2] varchar(100) , [Column3] varchar(100)) ; INSERT INTO MiscValues ([docID],[rowNumber],[Column1], [Column2], [Column3]) VALUES ('D0001',1, 'PartNumber', 'X0455', NULL), ('D0001',2, 'Prio', '1', NULL), ('D0001',3, 'Plant1', NULL, NULL), ('D0001',4, 'Plant2', 'PlantB', NULL), ('D0001',5, 'Plant3', 'PlantC', NULL), ('D0001',6, 'Plant4', NULL, NULL), ('D0001',7, 'Color1', 'white', NULL), ('D0001',8, 'Color2', 'black', NULL), ('D0001',9, 'Color3', 'blue', NULL), ('D0001',10, 'Material', 'MA123', NULL), ('D0001',11, 'Length', NULL, '10.87'), ('D0002',1, 'PartNumber', 'X0326', NULL), ('D0002',2, 'Prio', '2', NULL), ('D0002',3, 'Plant1', 'PlantA', NULL), ('D0002',4, 'Plant2', NULL, NULL), ('D0002',5, 'Plant3', 'PlantC', NULL), ('D0002',6, 'Plant4', 'PlantD', NULL), ('D0002',7, 'Color1', NULL, NULL), ('D0002',8, 'Color2', 'black', NULL), ('D0002',9, 'Color3', NULL, NULL), ('D0002',10, 'Color4', 'yellow', NULL), ('D0002',11, 'Material', 'MA456', NULL), ('D0002',12, 'Length', NULL, '16.43') ; CREATE TABLE LookUp([docID] varchar(10), [docType] varchar(100), [PartNumber] varchar(100), [internalID] varchar(100)); INSERT INTO LookUp([docID],[docType],[PartNumber], [internalID]) VALUES ('D0305415', 'docTypeSub', 'X0455', null), ('D0157632', 'docTypeMain', null, 'XY05570-XY05571'), ('D0181511', 'docTypeMain',null, 'XY05572-XY05573'), ('D0157633', 'docTypeMain', null, 'XY06380-XY06381'), ('D0156037', 'docTypeSub', 'X0326', null), ('D0151874', 'docTypeMain', null, 'XY05345'); CREATE TABLE Links ([docIDTop] varchar(10), [docIDBottom] varchar(10)); INSERT INTO Links ([docIDTop],[docIDBottom]) VALUES ('D0157632', 'D0305415'), ('D0181511', 'D0305415'), ('D0157633', 'D0305415'), ('D0151874', 'D0156037'); WITH CTE_no_nums AS ( SELECT docID, CASE WHEN PATINDEX('%[0-9]%',column1) > 0 THEN SUBSTRING(column1,0,PATINDEX('%[0-9]%',column1)) ELSE column1 END AS cols, COALESCE(column2,column3) AS vals FROM miscValues WHERE column2 IS NOT NULL OR column3 IS NOT NULL ), CTE_Pivot AS ( SELECT docID,partNumber,prio,[length],material FROM CTE_no_nums PIVOT ( MAX(vals) FOR cols IN (partNumber,prio,[length],material) ) pvt ), CTE_InternalIDs AS ( SELECT * ,STUFF ( (SELECT ', ' + internalID FROM LookUp AS L2 INNER JOIN Links L ON L2.docID=L.docIDTop WHERE L2.internalID IS NOT NULL AND L.docIDBottom=L1.docID FOR XML PATH('') ),1,2,'') AS ConcatenatedInternalIDs FROM LookUp AS L1 WHERE L1.internalID IS NULL ) SELECT A.docId + ' # ' + B.vals AS [DocID # Plant], A.docID, A.partNumber, A.prio, B.vals AS Plant, A.partNumber + '#' + A.material + '#' + A.[length] AS Identification, A.[length], SUBSTRING(CA.colors,0,LEN(CA.colors)) colors, --substring removes last comma IIDs.ConcatenatedInternalIDs FROM CTE_Pivot A INNER JOIN CTE_no_nums B ON A.docID = B.docID AND B.cols = 'Plant' INNER JOIN CTE_InternalIDs AS IIDs ON A.partNumber = IIDs.PartNumber CROSS APPLY ( SELECT vals + ',' FROM CTE_no_nums C WHERE cols = 'Color' AND C.docID = A.docID FOR XML PATH('') ) CA(colors) ; --Clean up... /* DROP TABLE Links; DROP TABLE LookUp; DROP TABLE miscValues; */

The above leads to the following:

 D0001 # PlantB D0001 X0455 1 PlantB X0455#MA123#10.87 10.87 white,black,blue XY05570-XY05571, XY05572-XY05573, XY06380-XY06381 D0001 # PlantC D0001 X0455 1 PlantC X0455#MA123#10.87 10.87 white,black,blue XY05570-XY05571, XY05572-XY05573, XY06380-XY06381 D0002 # PlantA D0002 X0326 2 PlantA X0326#MA456#16.43 16.43 black,yellow XY05345 D0002 # PlantC D0002 X0326 2 PlantC X0326#MA456#16.43 16.43 black,yellow XY05345 D0002 # PlantD D0002 X0326 2 PlantD X0326#MA456#16.43 16.43 black,yellow XY05345

EDIT: from here you will find my first answer (for understanding Juan Ruiz's answer):

I'm not quite sure if I understood you correctly ... You want to add a combined list to your query with all the internal identifiers associated with PartNumber LookUp.

You have a problem: there is no implicit sort order ...

Insert

  VALUES ('D0305415', 'docTypeSub', 'X0455', null), ('D0157632', 'docTypeMain', null, 'XY05570-XY05571'), ('D0181511', 'docTypeMain',null, 'XY05572-XY05573'), ('D0157633', 'docTypeMain', null, 'XY06380-XY06381'), ('D0156037', 'docTypeSub', 'X0326', null), ('D0151874', 'docTypeMain', null, 'XY05345');

seems to "bind" the values ​​"XY05570-XY05571", "XY05572-XY05573" and "XY06380-XY06381" to PartNumber "X0455" and the value "XY05345" in PartNumber "X0326". But it's not right!

You can do it like this:

 VALUES ('D0305415', 'docTypeSub', 'X0455', null), ('D0157632', 'docTypeMain', 'X0455', 'XY05570-XY05571'), ('D0181511', 'docTypeMain','X0455', 'XY05572-XY05573'), ('D0157633', 'docTypeMain', 'X0455', 'XY06380-XY06381'), ('D0156037', 'docTypeSub', 'X0326', null), ('D0151874', 'docTypeMain', 'X0326', 'XY05345');

Or you can add an IDENTITY column and play with all the entries between those that have internalID IS NULL.

With the first one (fill in the PartNumber column for each row), you can get the combined list as follows:

  select c.docType AS c_docTypeSub, c.docID AS C_docID, c.PartNumber AS C_PartNumber , b.docIDTop AS B_docIdTop, b.docIDBottom AS B_docIdBottom, a.*, STUFF((SELECT ', ' + x.internalID FROM LookUp AS x WHERE x.PartNumber=c.PartNumber FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,2,'') AS ConcatInternalID FROM LookUp a, Links b, LookUp c WHERE a.docType = 'docTypeMain' and a.docID = b.docIDTop and b.docIDBottom = c.docID and c.docType = 'docTypeSub' ;

From couse, you can also add this to your "old request":

Just add this to the final SELECT

 [...CTEs before...] SELECT A.docId + ' # ' + B.vals AS [DocID # Plant], A.docID, A.partNumber, A.prio, B.vals AS Plant, A.partNumber + '#' + A.material + '#' + A.[length] AS Identification, A.[length], SUBSTRING(CA.colors,0,LEN(CA.colors)) colors, --substring removes last comma STUFF((SELECT ', ' + x.internalID FROM LookUp AS x WHERE x.PartNumber=A.PartNumber FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,2,'') AS ConcatInternalID [...FROM...]

I hope I understand it well and it can help you ...

+2
source

Based on @Shnugo's answer, try this non-unoptimized (yet) answer, I think that @Shnugo does not check the link table because you do not need to change the data data:

 [...CTEs before...] SELECT A.docId + ' # ' + B.vals AS [DocID # Plant], A.docID, A.partNumber, A.prio, B.vals AS Plant, A.partNumber + '#' + A.material + '#' + A.[length] AS Identification, A.[length], SUBSTRING(CA.colors,0,LEN(CA.colors)) colors, --substring removes last comma STUFF((SELECT ', ' + X.internalID FROM LOOKUP X INNER JOIN LINKS Z ON X.DOCID = Z.DOCIDTOP INNER JOIN LOOKUP X2 ON X2.DOCID = Z.DOCIDBOTTOM WHERE X2.PartNumber=A.PartNumber FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,2,'') AS ConcatInternalID [...FROM...]
+2
source

Source: https://habr.com/ru/post/1231921/

More articles:


All Articles