How to update a column based on multiple string of another column in SQL Server?

Question

I have a table with two strings in a column. I am trying to update the multicode column based on the EN in Lookup table. Below are the tables:

#Lookup table

EN	ID
Annual meadow grass	45
Growth regulation	828
Leaf scald	971
Weeds - Broadleaf	1997

#Table that must be updated

ResponseCode	EN	Multicode
6d5j87	Annual meadow grass,Weeds - Broadleaf	NULL
6d5j87	Growth regulation, Leaf scald	NULL
6d5j87	Leaf scald, Weeds - Broadleaf	NULL

#Expected Table output

ResponseCode	EN	Multicode
6d5j87	Annual meadow grass,Weeds - Broadleaf	45,1997
6d5j87	Growth regulation	828
6d5j87	Leaf scald, Weeds - Broadleaf	971,1997

Current code:

select EN, ID From #TargetFlatten1
select ResponseCode, EN, Multicode From #targettest

Answer 1

One option turns the CSV string to rows with string_split(), does the lookup, then aggregates back the codes with string_agg:

select t.responseCode, t.en, x.multicode
from mytable t
cross apply (
    select string_agg(l.responseCode) within group (order by ordinal) as multicode
    from string_split(t.en, ',', 1)
    inner join mylookup l on l.en = value
) x

value and ordinal are two columns generated by string_split wich store (resp) the single value and its original position in the CSV list.

If you wanted an update, we can turn the logic to a correlated subquery:

update t
set t.multicode = (
    select string_agg(l.responseCode) within group (order by ordinal)
    from string_split(t.en, ',', 1)
    inner join mylookup l on l.en = value
)
from mytable t