TSQL - extend query consisting of cross apply and pivot

Question

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.

Your two fiddle data sets do not appear to share any commonality. If, in fact, you would be able to look up from a PartNumber in the MiscValues table to a PartNumber in the LookUp table, I think your problem solves itself. Redo the data and you should be able to figure it from there. Similarly if the docID fields in LookUp or Links match docIDs in Document or MiscValues, your problem is solved. If this is not the case, you have no relational data, and this is not possible. — Thomas Steven, Sep 24 '15 at 13:03
Hi Tony, it would be nice if you'd vote helpful answers up and accept the one which helped you to solve your problem, thx! — Shnugo, Nov 11 '15 at 21:39

Shnugo · Answer 1 · 2015-09-27T14:39:57.303

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...

Juan Ruiz de Castilla · Answer 2 · 2015-09-27T03:38:09.757

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...]

Thank you for opening my eyes for the Links table... You are right, that the missing information is to be found there... Changed my answer. One upvote for you from me — Shnugo, Sep 27 '15 at 14:45

TSQL - extend query consisting of cross apply and pivot

2 Answers2