SQL Server 2019

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

create table table1 (id int, code varchar(100), "type" varchar(100));
create table table2 (id int, code varchar(100), "text" varchar(100));
insert into table1 values (1,'A001','A,B');
insert into table1 values (2,'B001','A,B');
insert into table1 values (3,'C001','A,B');
3 rows affected

insert into table2 values (1,'A001','Text A');
insert into table2 values (2,'B001','Text B');
insert into table2 values (3,'C001','Text C');
3 rows affected

SELECT ROW_NUMBER() OVER (ORDER BY t1.code) AS id,
t1.code, t2.text, sp.value AS type
FROM table1 AS t1 
OUTER APPLY STRING_SPLIT(t1.type, ',') sp
JOIN table2 AS t2 
ON t1.code = t2.code;


id
code
text
type


1
A001
Text A
A

2
A001
Text A
B

3
B001
Text B
A

4
B001
Text B
B

5
C001
Text C
A

6
C001
Text C
B

id	code	text	type
1	A001	Text A	A
2	A001	Text A	B
3	B001	Text B	A
4	B001	Text B	B
5	C001	Text C	A
6	C001	Text C	B