TSQL - 扩展由交叉应用和数据透视组成的查询

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.

关于输出,我需要根据PartNumber列在逗号分隔列中显示新的internalID列。

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:

我的问题是将这些部分放在一起,以便我可以在下面的旧查询中显示InternalID:

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

2 个解决方案

#1

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:

基于@Shnugo回答尝试这个未经优化(尚未)回答,我认为@Shnugo没有检查链接表,因为您不需要更改您的数据信息:

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

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:

这是我的最终建议,在另外一个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):

编辑:从这里开始,你会找到我的第一个答案(了解Juan Ruiz的回答):

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.

我不完全确定,如果我理解正确的话...你想在查询中添加一个连接列表,所有的internalID条目都适合LookUp的PartNumber。

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

似乎将值“XY05570-XY05571”,“XY05572-XY05573”和“XY06380-XY06381”“绑定”到PartNumber“X0455”,将值“XY05345”“绑定”到PartNumber“X0326”。但这是错误的!

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.

或者,您可以添加IDENTITY列,并使用internalID IS NULL之间的所有条目进行操作。

With the first (fill the PartNumber column for each row) you can get the concatenated list like this:

使用第一行(填充每行的PartNumber列),您可以获得如下的连接列表:

    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

只需将其添加到最终的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...

希望我能理解这一点,这可以帮助你......