Replace value in Column1 of Table 1 with using values from Rows in Table2

Question

I Have one table with column values like Below -

Table1

id Key_value
1  1|2|3
2  1|3|2

Table2

id  Name
1   A
2   B
3   C

I need output of Table1 like

Id  key_value

1   A,B,C
2   A,C,B

Answer 1

You can join and use string_agg():

select t1.*, s.names
from table1 t1 cross apply
     (select string_agg(t2.name, ',') as names
      from string_split(t1.key_value, '|') s join
           table2 t2
           on t2.id = s.value
     ) s;

Note: The names are in an arbitrary order. If you want to preserve the original order, you can do:

select t1.*, s.names
from table1 t1 cross apply
     (select string_agg(t2.name, ',') within group (order by charindex('|' + s.value + '|', '|' + t1.key_value + '|')) as names
      from string_split(t1.key_value, '|') s join
           table2 t2
           on t2.id = s.value
     ) s;

Also: You should not be storing multiple values in a string for many good reasons. You should work on fixing the data model. Here are some reasons:

• It violates the notion that a column contains a single value.
• Foreign key relationships should be properly defined.
• Numbers should be stored as numbers.
• SQL in general and SQL Server in particular have pretty lousy string processing functions.
• The queries cannot be optimized easily using indexes and partitions.

Answer 2

Gordon Linoff's second query is fine, unless there are duplicate in the CSV list, in which case it cannot guarantee that the names will follow the original ordering of the ids.

Here is a more generic approach, that works regardless of whether there are duplicates or not. The idea is to use a recursive query to split the CSV string, which allows keeping track of the original position of each element in the list:

with cte as (
    select id, cast(null as nvarchar(max)) val, key_value + '|' rest, 0 lvl
    from table1
    union all
    select id, substring(rest, 1, charindex('|', rest) - 1), substring(rest, charindex('|', rest) + 1, len(rest)), lvl + 1
    from cte
    where charindex('|', rest) > 0
)
select c.id, string_agg(t2.name, '|') within group (order by c.lvl) names
from cte c
inner join table2 t2 on t2.id = c.val
where c.lvl > 0 
group by c.id