How To Update a Row With Multiple Combined Rows In SQL Server

I have two tables as decribe bellow:

Table1

id | table2_id | value
1  | 1         | AAA
2  | 1         | BBB
3  | 1         | CCC

Table2

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?

Answers


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

Need Your Help

LINQ, Merging Lists with appropriate 'Overrides'

c# linq

This situation may be far more complicated than I want it to be, but I am bringing it forth anyway. This is for a game-type design.

Is using std::vector< std::shared_ptr<const T> > an antipattern?

c++ templates c++11 c++-standard-library

For a long time I was using std::vector and std::shared_ptr hand in hand. Recently I started using std::shared_ptr&lt;const T&gt; whenever a pointer to a const object was needed. This is all OK, si...