sql - 如何在 SQL Server 中更新具有多个组合行的行

Question

我有两个表如下所述:
Table1

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

Table2

id | value
1  | XXXX

我想更新 Table2合并值来自 Table1 .

预期输出 Table2

id | value
1  | AAA,BBB,CCC

我该怎么做或者有最好的方法来获得上面解释的预期结果，只是在 sql server 中使用 TSQL？

Answer 1

您可以使用 Common Table Expression对此，

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

SQLFiddle Demo