如何将逗号分隔的列值与另一个表连接为行 [英] How to join comma separated column values with another table as rows

问题描述

我试图通过首先从我成功完成的SupplierId"列转换逗号分隔值来连接两个表.但是，当我尝试通过外键DCLink"加入具有供应商名称的另一个表供应商"时，问题就出现了.

I am trying to join two tables by first converting a comma separated values from a column "SupplierId" which I am doing successfully. However, the issue comes in when I try joining to another table 'Vendors' with the Supplier names via a foreign key 'DCLink'.

这就是我的意思:

原始表的选择语句，

  SELECT  InquiryId, SupplierId FROM Procure_InquiryDetails

给出这个结果

InquiryId   SupplierId

1           2,3
2           175
3           170,280
5           
7           12
8           5,9

我可以使用此 sql 语句从 SupplierId 拆分列

I am able to split the columns from SupplierId using this sql statement

;WITH CTE
    AS
    (
        SELECT  InquiryId,
                [xml_val] = CAST('<t>' + REPLACE(SupplierId,',','</t><t>') + '</t>' AS XML)
        FROM Procure_InquiryDetails
    )

SELECT  InquiryId,
        [SupplierId] = col.value('.','VARCHAR(100)')
FROM CTE
CROSS APPLY [xml_val].nodes('/t') CA(col) 

并得到这些结果

InquiryId   SupplierId
    1           2
    1           3
    2           175
    3           170
    3           280
    5   
    7           12
    8           5
    8           9 

然而，当我应用这段代码将 InquiryDetails 表连接到供应商名称的 Vendor 表时，

When I apply this bit of code to join the InquiryDetails table to the Vendor Table on supplier Name however,

;WITH CTE
AS
(
    SELECT  InquiryId,
            [xml_val] = CAST('<t>' + REPLACE(SupplierId,',','</t><t>') + '</t>' AS XML),
            Vendor.Name
    FROM Procure_InquiryDetails inner join Vendor
    on ',' + Procure_InquiryDetails.SupplierId + ',' like '%,' + cast(Vendor.DCLink as nvarchar(20)) + ',%'
)

SELECT  InquiryId, Name,
        [SupplierId] = col.value('.','VARCHAR(100)')
FROM CTE
CROSS APPLY [xml_val].nodes('/t') CA(col)

它给了我这个非常不方便的结果:

It gives me this very inconvenient result:

InquiryId   Name                                                                                                                                                   SupplierId
----------- ------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------
1           Accesskenya Group Ltd                                                                                                                                  2
1           Accesskenya Group Ltd                                                                                                                                  3
1           Aquisana Ltd                                                                                                                                           2
1           Aquisana Ltd                                                                                                                                           3
2           TOYOTA KENYA                                                                                                                                           175
3           Institute of Chartered Shipbrokers ICS-USD                                                                                                             170
3           Institute of Chartered Shipbrokers ICS-USD                                                                                                             280
7           CMA CGM Kenya Ltd                                                                                                                                      12
8           Aon Kenya Insurance Brokers Ltd                                                                                                                        5
8           Aon Kenya Insurance Brokers Ltd                                                                                                                        9
8           Bill investments ltd                                                                                                                                   5
8           Bill investments ltd

我希望 join 语句像原始的 select 语句一样显示和流动.

I would wish for the join statement to show and flow as the original select statement.

我被卡住了，我似乎无法弄清楚我哪里出错了.任何指向正确方向的指针?

I am stuck and I cannot seem to figure out where I am going wrong. Any pointers in the right direction?

推荐答案

假设您使用的是 SQL Server 2016，您可以使用 string_split() 来解析您的 CSV 列(除了:逗号-字段中的分隔值表明数据模型不佳)，而无需求助于 CTE 或 XML 方法.

Assuming you're using SQL Server 2016, you can use string_split() to parse out your CSV column (aside: comma-separated values in a field is a sign of a poor data model) without resorting to a CTE or XML methods.

select I.inquiry_id, sup.value,V.Name
from Procure_InquiryDetails I
CROSS APPLY string_split(I.supplier_value,',') sup
join Vendor v on v.DCLink = sup.value

这篇关于如何将逗号分隔的列值与另一个表连接为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！