How to join comma separated column values with another table as rows

Question

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

This is what I mean:

The select statement for the Original Table,

  SELECT  InquiryId, SupplierId FROM Procure_InquiryDetails

Gives this result

InquiryId   SupplierId

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

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) 

and get these results

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

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

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?

Smyrna

Which version of Sql server? – Intracranial

Sql Server 2014 – Smyrna

Why do you even store comma-separated values in a column? You should build a proper normalized relational datamodel instead, so such issues cannot occur in the first place. – Lakisha

Its an inherited db. If I had my way, I wouldn't work with it, but duty calls – Smyrna

the desired result would be for it to mimic the original select statement, I edited the question. Thanks. – Smyrna

@Chesaro you can check the following link for many string splitting user function that can be used in sql server 2014: https://mcmap.net/q/134533/-t-sql-split-string – Capability

@Capability , very useful link. thank you. – Smyrna

@ThorstenKettner, you can still use it as normalized using IN STRING_SPLIT (PID.SupplierId,','), rather than = to – Egocentric

Answer 1

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

Viridity

how about sql server 2014? – Smyrna

Then you can replace string_split() with any other string-splitting function which you can find on the internet. – Viridity

alroc, I should've said this sooner. Thank you so much. I combined your answer and modified it using the function on @Hadi's link and voila! – Smyrna

Answer 2

You've forgotten to supply expected results, so this is a stab in the dark, however, what's wrong with splitting your string and using the results with a JOIN:

SELECT {Needed Columns}
FROM dbo.Procure_InquiryDetails PID
     CROSS APPLY STRING_SPLIT(PID.SupplierId,',') SS
     JOIN dbo.Vendor V ON SS.[value] = V.SupplierID;

Ideally, however, you shouldn't be storing delimited data in your RDBMS. Considering switching to a proper normalised many-to-many relationship structure.

If you're still on SQL Server 2008 (to which I would highly recommend you upgrade), then you can use delimitedsplit8k, or on 2012/2014 you can use delimitedsplit8k_lead.

Vershen

Your answer is quite clear thank you very much, I am however wondering how the code you supplied above would implement delimitedsplit8k_lead on my sql server 2014 without needing a function declaration. This is due to the fact that I am using the sql string on a c# app – Smyrna

You would need to create the function first in the relevant database and call that instead. Also, I believe that both functions return the column item (and itemnumber) not [value]. – Vershen

@Larnu i removed my answer after checking delimitedsplit8k function that you mentioned. I totally agree with you it is better to use it. +1 – Capability