MySQL 8.0

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

select version();


version()


8.0.30


CREATE TABLE products ( 
Product_ID INT,
Product_Code VARCHAR(20) ,
Assign_Key VARCHAR(20) );

INSERT INTO  products  VALUES
("1","ABC","23,45,23"),
("1","HIJ","23"),
("1","KLM","45"),
("2","DEF","10,28"),
("2","GHI","10"),
("2","NOP","28");

Records: 6  Duplicates: 0  Warnings: 0


CREATE TABLE assign ( 
Product_ID INT ,
Assign_ID INT ,
Value_ID INT );

INSERT INTO  assign  VALUES
("1","23","1"),
("1","45","2"),
("2","10","3"),
("2","28","4");

Records: 4  Duplicates: 0  Warnings: 0


-- I named the table `value`, because `values` is a reserved word.
CREATE TABLE value ( 
Value_ID INT ,
Value_name VARCHAR(20) );

INSERT INTO  value  VALUES
("1","Red"),
("2","Large"),
("3","Blue"),
("4","Small");

Records: 4  Duplicates: 0  Warnings: 0


select p.product_id, p.product_code, p.assign_key,
  group_concat(a.value_id   order by j.seq) value_ids,
  group_concat(v.value_name order by j.seq) value_names
from products p
cross join json_table(
    concat('[', p.assign_key, ']'),
    '$[*]' columns ( seq for ordinality, assign_id int path '$')
) j
inner join assign a on a.assign_id = j.assign_id
inner join value v on v.value_id  = a.value_id
group by p.product_id, p.product_code, p.assign_key


product_id
product_code
assign_key
value_ids
value_names


1
ABC
23,45,23
1,2,1
Red,Large,Red

1
HIJ
23
1
Red

1
KLM
45
2
Large

2
DEF
10,28
3,4
Blue,Small

2
GHI
10
3
Blue

2
NOP
28
4
Small


select p.*, x.*
from products p
cross join lateral (
    select 
        group_concat(a.value_id   order by j.seq) value_ids,
        group_concat(v.value_name order by j.seq) value_names
    from json_table(
        concat('[', p.assign_key, ']'),
        '$[*]' columns ( seq for ordinality, assign_id int path '$')
    ) j
    inner join assign a on a.assign_id = j.assign_id
    inner join value v on v.value_id  = a.value_id
) x


Product_ID
Product_Code
Assign_Key
value_ids
value_names


1
ABC
23,45,23
1,2,1
Red,Large,Red

1
HIJ
23
1
Red

1
KLM
45
2
Large

2
DEF
10,28
3,4
Blue,Small

2
GHI
10
3
Blue

2
NOP
28
4
Small

product_id	product_code	assign_key	value_ids	value_names
1	ABC	23,45,23	1,2,1	Red,Large,Red
1	HIJ	23	1	Red
1	KLM	45	2	Large
2	DEF	10,28	3,4	Blue,Small
2	GHI	10	3	Blue
2	NOP	28	4	Small