How To Update a Row With Multiple Combined Rows In SQL Server
I have two tables as decribe bellow:
id | table2_id | value 1 | 1 | AAA 2 | 1 | BBB 3 | 1 | CCC
id | value 1 | XXXX
and I want to update Table2 with combined value from Table1.
OUTPUT Expected on Table2
id | value 1 | AAA,BBB,CCC
How can I do or there are the best way to get expected result that explained above just using TSQL in sql server?
you can use Common Table Expression on this,
WITH record AS ( SELECT [table2_id], STUFF((SELECT ',' + [value] FROM Table1 WHERE [table2_id] = a.[table2_id] FOR XML PATH ('')), 1, 1, '') AS val_list FROM Table1 AS a GROUP BY [table2_id] ) UPDATE a SET a.value = b.val_list FROM table2 a INNER JOIN record b ON a.ID = b.table2_id