Menu
this question is based on my Previous Question. I need to extend the query so that I can incorporate two other tables (running on another server instance).
In this Fiddle I added those 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');
Regarding the output I need to display the new internalID column in a comma-separated column based on the PartNumber column.
This is the query that outputs 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 put those pieces together so that I can get the InternalID to show 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)
;
Hope you can show me how this can be achieved. If something is unclear feel free to ask. And no, I'm not in charge of the data structure :-)
Thank you.
233
Based on @Shnugo answer try this not unoptimized (yet) answer, I think that @Shnugo doesn´t check the link table, because you don't need to change your data information:
[...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...]
Juan Ruiz de Castilla extended my given answer and opened my eyes for your "Links"-table.
This is my final suggestion, resolving your problem in one more 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 comes back with this:
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 on you'll find my first answer (for understanding Juan Ruiz' answer):
I'm not absolutely sure, if I understood you correctly... You want to add a concatenated list to your query with all the internalID entries fitting to the PartNumber of LookUp.
The problem you have: There is no implicit sort order...
Your insertion of
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 the PartNumber "X0455" and the value "XY05345" to the PartNumber "X0326". But this is wrong!!!
You can either 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 fiddle around with all entries between those having internalID IS NULL.
With the first (fill the PartNumber column for each row) you can get the concatenated list like this:
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'
;
Of couse you can add this to your "old query" as well:
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...]
Hope I understood this well and this can help you...
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
