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...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.