SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table1 (id int, data_1 int, SHIFT_ID varchar(100));
insert into table1 values
(1,100,'["1","2","3"]'),
(2,150,'["1","3"]'),
(3,50 ,'["3"]');
3 rows affected

create table table2 (id int, shift_name varchar(100));
insert into table2 values
(1,'Morning'),
(2,'Afternoon'),
(3,'Night');
3 rows affected

-- Build a CTE with id, data_1 and shift_id
-- Foreach shift_id from the comma-separated list, an own row will be created 
WITH t1
AS
  (SELECT 
    id, data_1, value AS shift_id
   FROM table1
   -- Split the comma-separated list into rows
   CROSS APPLY OPENJSON(shift_id))
SELECT 
  t1.id, t1.data_1, 
  -- Build a comma-separated list of the shift names
  -- Note1: Are you sure this column should be named shift_id and not shift_names?
  -- Note2: You might add an ORDER BY clause in STRING_AGG if intended
  STRING_AGG(t2.shift_name, ', ') AS shift_id
FROM 
  -- Join the CTE which is a "redesigned" table1 with table2
  t1 INNER JOIN table2 t2
    ON t1.shift_id = t2.id
GROUP BY t1.id, t1.data_1
ORDER BY t1.id;


id
data_1
shift_id


1
100
Morning, Afternoon, Night

2
150
Morning, Night

3
50
Night