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?
In the Split Cells dialog box, select Split to Rows or Split to Columns in the Type section as you need. And in the Specify a separator section, select the Other option, enter the comma symbol into the textbox, and then click the OK button.
Merging tables by columns. Multiple tables can be merged by columns in SQL using joins. Joins merge two tables based on the specified columns (generally, the primary key of one table and a foreign key of the other).
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.
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