基于多个子表行的SQL更新父表字段。

I have the following sample table structures for a parent / child relationship.

对于父/子关系，我有以下示例表结构。

parent table

父表

id    LongText
--------------
10    
20

child table

子表

id    char     value
--------------------
10    COLOR    RED
10    HEIGHT   1FT
20    COLOR    BLUE
20    WIDTH    2FT

I have a requirement that specifies that certain fields from the child table rows need to be concatenated together and placed in the parent table. I would like to complete this in a single SQL query if possible. The update statement that I have written is as follows.

我有一个需求，它指定子表行的某些字段需要被连接在一起并放在父表中。如果可能的话，我希望在一个SQL查询中完成这个操作。我编写的更新语句如下。

UPDATE
  parent
SET
  LongText = COALESCE(LongText, N'')
              + child.char + N': ' + child.val + ','
FROM
  parent INNER JOIN child
    ON
      parent.id = child.id

But I only get the following results.

但我只得到以下结果。

id    LongText
------------------
10    COLOR: RED,
20    COLOR: BLUE,

I would expect (or I should say I WANT) to get this.

我期望(或者我应该说我想)得到这个。

id    LongText
------------------
10    COLOR: RED,HEIGHT: 1FT
20    COLOR: BLUE,WIDTH: 2FT

Is this possible? Any suggestions as to how I can do this? Any help is appreciated!

这是可能的吗?关于我如何做这件事有什么建议吗?任何帮助都是赞赏!

Here is a SQLfiddle for reference.

这里有一个SQLfiddle作为参考。

1 个解决方案

#1

update parent
set longtext =
    stuff((
    select ',' + c.char + ': ' + c.val
    from child c
    where c.uid = parent.uid
    for xml path(''), type).value('.','nvarchar(max)'),1,1,'');

I have updated your SQLFiddle with the solution.

我已经更新了你的SQLFiddle解决方案。

STUFF() function to remove the leading ',' (comma) from the first characteristic.

函数的作用是:从第一个特征中删除前导'，'(逗号)。
FOR XML to create an XML document from a query result. This is a well known trick with SQL Server - because the column is not named, there is no element produced and only the raw text (of each row) is output, mashed together into a single row.

让XML从查询结果创建XML文档。这是使用SQL Server的一个众所周知的技巧——因为该列没有命名，所以没有生成元素，只有原始文本(每行)是输出，并将它们合并成一行。

Very few articles on the internet attempt to explain it in detail, since the code is pretty much left as an explanation in and of itself.

互联网上很少有文章试图详细地解释它，因为代码本身基本上只是一种解释。

#1

update parent
set longtext =
    stuff((
    select ',' + c.char + ': ' + c.val
    from child c
    where c.uid = parent.uid
    for xml path(''), type).value('.','nvarchar(max)'),1,1,'');