Join string_split values within SQL

Question

Good day,

im currently working on a SQL query that got kinda messy and i dont know how to accomplish the last step i need. The base construct is based on 3 tables:

dbo.csv_export_160_Gewindefahrwerke The main data table
dbo.TecDoc Table with additional informations joined with an INNER JOIN
dbo.KW Hinweise Table with an ìdentifier column that needs to be added to the main data table

So far so good, everything works except the addition of the 3rd table. The column containing the data to be joined on are comma seperated values inside the 1st table (dbo.csv_export_160_Gewindefahrwerke) but differ from row to row. Something like this:

5,13,47,64
1,10,50,55,74,88
7,14,26,30,31,40

Those values need to be joined one by one with the 3rd table dbo.KW Hinweise to get the text of those (dbo.KW Hinweise.Text).

I already got some sort of solution in my other question: SQL join on string_split values, but dont know how to implement it so it actually works. Someone got an idea?

Also: Heres the current query for reference: https://pastebin.pl/view/8b9105ef

Programm used: MS SQL Server Manager Studio

Version: Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64) Nov 2 2020 19:19:59 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 10.0 (Build 18363: )

You're on SQL server 2017; what's wrong with `STRING_SPLIT`? — Thom A, Jan 19 '21 at 10:41
i dont really know to to implement it into the current query to get the data from table3 into the query — Marcel H., Jan 19 '21 at 10:42
Have you had a look at the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15) on it? It comes with [examples](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15). What have you tried, why didn't it work? — Thom A, Jan 19 '21 at 10:43
Does this answer your question? [How to join comma separated column values with another table as rows](https://stackoverflow.com/q/54065989/2029983) — Thom A, Jan 19 '21 at 10:44

score 1 · Answer 1 · answered Jan 19 '21 at 11:58

Applying the string_split() function that was suggested in the comments gives me the following solution.

Sample data

create table csv_export_160_Gewindefahrwerke
(
  id nvarchar(1),
  export nvarchar(200)
);
insert into csv_export_160_Gewindefahrwerke (id, export) values
('A', '5,13,47,64'),
('B', '1,10,50,55,74,88'),
('C', '7,14,26,30,31,40');

create table TecDoc
(
  id nvarchar(1),
  doc nvarchar(10)
);
insert into TecDoc (id, doc) values
('A', 'alpha'),
('B', 'beta'),
('C', 'gamma');

create table KW_Hinweise
(
  identifier int,
  description nvarchar(10)
);
insert into KW_Hinweise (identifier, description) values
(1, 'One'),
(5, 'Five'),
(7, 'Seven'),
(10, 'Ten');

Solution

select csv.id,
       csv.export,
       td.doc,
       s.value,
       kw.description
from csv_export_160_Gewindefahrwerke csv
join TecDoc td
  on td.id = csv.id
cross apply string_split(csv.export, ',') s
left join KW_Hinweise kw -- using left join because I only provide a few sample rows
  on kw.identifier = s.value;

Result

id export            doc   value description
-- ----------------- ----- ----- -----------
A   5,13,47,64       alpha 5     Five
A   5,13,47,64       alpha 13    null
A   5,13,47,64       alpha 47    null
A   5,13,47,64       alpha 64    null
B   1,10,50,55,74,88 beta  1     One
B   1,10,50,55,74,88 beta  10    Ten
B   1,10,50,55,74,88 beta  50    null
B   1,10,50,55,74,88 beta  55    null
B   1,10,50,55,74,88 beta  74    null
B   1,10,50,55,74,88 beta  88    null
C   7,14,26,30,31,40 gamma 7     Seven
C   7,14,26,30,31,40 gamma 14    null
C   7,14,26,30,31,40 gamma 26    null
C   7,14,26,30,31,40 gamma 30    null
C   7,14,26,30,31,40 gamma 31    null
C   7,14,26,30,31,40 gamma 40    null

Fiddle to see it in action.

Join string_split values within SQL

1 Answers1