SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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');

select * from csv_export_160_Gewindefahrwerke;


id
export


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');

select * from TecDoc;


id
doc


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');

select * from KW_Hinweise;


identifier
description


1
One

5
Five

7
Seven

10
Ten


select csv.id,
       csv.export,
       td.doc,
       s.* --> the result of the string_split() function is a single column 'value'
from csv_export_160_Gewindefahrwerke csv
join TecDoc td
  on td.id = csv.id
cross apply string_split(csv.export, ',') s;


id
export
doc
value


A
5,13,47,64
alpha
5

A
5,13,47,64
alpha
13

A
5,13,47,64
alpha
47

A
5,13,47,64
alpha
64

B
1,10,50,55,74,88
beta
1

B
1,10,50,55,74,88
beta
10

B
1,10,50,55,74,88
beta
50

B
1,10,50,55,74,88
beta
55

B
1,10,50,55,74,88
beta
74

B
1,10,50,55,74,88
beta
88

C
7,14,26,30,31,40
gamma
7

C
7,14,26,30,31,40
gamma
14

C
7,14,26,30,31,40
gamma
26

C
7,14,26,30,31,40
gamma
30

C
7,14,26,30,31,40
gamma
31

C
7,14,26,30,31,40
gamma
40


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;


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

id	export	doc	value
A	5,13,47,64	alpha	5
A	5,13,47,64	alpha	13
A	5,13,47,64	alpha	47
A	5,13,47,64	alpha	64
B	1,10,50,55,74,88	beta	1
B	1,10,50,55,74,88	beta	10
B	1,10,50,55,74,88	beta	50
B	1,10,50,55,74,88	beta	55
B	1,10,50,55,74,88	beta	74
B	1,10,50,55,74,88	beta	88
C	7,14,26,30,31,40	gamma	7
C	7,14,26,30,31,40	gamma	14
C	7,14,26,30,31,40	gamma	26
C	7,14,26,30,31,40	gamma	30
C	7,14,26,30,31,40	gamma	31
C	7,14,26,30,31,40	gamma	40

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