tsql - How can I concatenate values for each distinct key in a mappings table? -

i'm trying condense mappings table concatenated xml string. essentially, want take table:

old_key      old_value 1            'a' 1            'b' 1            'c' 2            'd' 2            'e' 

and insert values in col2 turned xml string each value in col1, so:

new_key new_value 1       <vals><val>a</val><val>b</val><val>c</val><vals> 2           <vals><val>d</val><val>e</val></vals> 

my current concatenation code is:

insert new_table (new_key, new_value) select distinct(old_key), (select old_value val old_table xml path(''), root('vals')) old_table 

this code doesn't work, since of old_values being concatenated together. how can make sure old_values share same key concatenated together? let me know if there's else can clarify situation. thanks!

join against outer old_table in sub query , use group by instead of distinct.

select   o1.old_key,   (select old_value val    old_table o2    o1.old_key = o2.old_key    xml path(''), root('vals'), type) keys   old_table o1 group o1.old_key 

result

old_key  keys -------  ------------------------------------------------- 1        <vals><val>a</val><val>b</val><val>c</val></vals> 2        <vals><val>d</val><val>e</val></vals>