Here's my take on the issue:

;WITH table1 AS (
SELECT  *
FROM    (
    VALUES  (1, N'test 1', N'Table1Value1,Table2Name1,Table3Name1')
    ,   (2, N'test 2', N'Table1Value2,Table2Name2,Table2Name4,Table3Name2')
) t (ID,Name,[values])
)
, table2 AS (
SELECT  *
FROM    (
    VALUES  (N'Table2Name1', N'A,B,C')
    ,   (N'Table2Name2', N'D,E,F')
    ,   (N'Table2Name3', N'G,H')
    ,   (N'Table2Name4', N'I,J,K')
) t (Name,[Values])
)
,table3 AS (
SELECT  *
FROM    (
    VALUES  (N'Table3Name1', N'Table2Name1,Table2Name3')
    ,   (N'Table3Name2', N'Table2Name2,Table2Name3')
) t (Name,[Values])
)
SELECT  id, name, STRING_AGG(val, ',') WITHIN GROUP (ORDER BY sort, val)
FROM    (
    SELECT  t1.id, t1.name, MIN(sort) AS sort
    ,   z.val
    FROM    table1 t1
    CROSS APPLY string_split(t1.[values], ',') x
    LEFT JOIN table2 t2
        ON  t2.name = x.value
    LEFT JOIN table3 t3
        ON  t3.[name] = x.value
    OUTER APPLY string_split(t3.[values], ',') x2
    LEFT JOIN table2 t22
        ON  t22.name = x2.value
    CROSS APPLY (
        SELECT  val, sort
        FROM    (
            VALUES  (CASE WHEN t2.name IS NULL AND t3.name IS NULL THEN x.value END, 1)
            ,   (t2.[values], 2)
            ,   (t22.[values],2)
            ) v (val, sort)
        WHERE   v.val IS NOT NULL
        ) z
    GROUP BY t1.id, t1.name, z.val
    ) x
GROUP BY id, name

I basically just follow the rabbithole of string splits from various tables. Finally, i collect the values from table2 together with what's left over from values in table1, do a grouping to remove duplicate and finally STRING_AGG them together to some fort of CSV string.

As others said, this database design ain't good, as Codd et.al. said, one should never combine multiple values into a single column, and definitely don't combine key / value data in the same column.

I tried but I am not sure it is not an optimized solution. But it is the exact solution. and more readable query.

My table names:
table1 (id int, nam varchar(100), val varchar(100))

table2 (name varchar(100),val varchar(100))

table3(name varchar(100), val varchar(100))

with cte as(select t1.id,t1.name,value as val from my_db.dbo.table1 t1 CROSS APPLY STRING_SPLIT(val, ',')),
cte2 as(select t1.id, t1.name, t1.val, case when t2.val is null then t1.val else t2.val end as val1 from cte t1 left join table2 t2 on t1.val=t2.name) ,
cte3 as(select ct2.id,ct2.name ,case when t3.val is null then ct2.val1 else t3.val end as val from cte2 ct2 left join table3 t3 on t3.name = ct2.val),
cte4 as(select id,name,value from cte3 CROSS APPLY STRING_SPLIT(val, ',')),
cte5 as(select ct4.id,ct4.name,case when t2.val is null then ct4.value else t2.val end as val from cte4 ct4 left join table2 t2 on ct4.value=t2.name),
cte6 as(select id,name as name,value as val from cte5 CROSS APPLY STRING_SPLIT(val, ',') group by id,name,value)
select id as 'ID',name as 'Name',STRING_AGG(val,', ') within group (order by case when len(val)>1 then 0 else 1 end, val) as 'Values' from cte6 group by id,name;