如何用来自另一个表中列的连接值的字符串填充表中的列

Question

我试图用另一个表中列的连接值字符串填充表中的列。有许多解决方案的建议，如如何连接文本从多行到一个单一的文本字符串在SQL Server，其中有47个答案，但没有一个是为我工作。

表@tbl1:

DECLARE @tbl1 TABLE ([Id] INT, [Value] VARCHAR(10))
INSERT INTO @tbl1 ([Id]) VALUES (1),(2),(3)
[Id]    [Value]
1       NULL
2       NULL
3       NULL

表@tbl2:

DECLARE @tbl2 TABLE ([Id] INT, [Value] VARCHAR(10))
INSERT INTO @tbl2 ([Id],[Value]) VALUES (1,'A'),(3,'B'),(1,'C'),(2,'D'),(2,'E'),(3,'F'),(1,'G')
[Id]    [Value]
1        A
3        B
1        C
2        D
2        E
3        F
1        G

我正在寻找语法来更新表@tbl1中的记录:

[Id]    [Value]
1       ACG
2       DE
3       BF

这行不通:

UPDATE [t1]
SET [t1].[Value] = COALESCE([t1].[Value],'') + [t2].[Value]
FROM @tbl1 AS [t1]
LEFT JOIN @tbl2 AS [t2] ON [t1].[Id] = [t2].[Id]

结果:

[Id]    [Value]
1        A
2        D
3        B

这个语法产生相同的结果:

UPDATE [t1]
SET [t1].[Value] = [t2].[Val]
FROM @tbl1 AS [t1]
OUTER APPLY (
SELECT COALESCE([tb2].[Value],[t1].[Value]) AS [Val]
FROM @tbl2 AS [tb2]
WHERE [tb2].[Id] = [t1].[Id]
) AS [t2]

将SET更改为SELECT(如下)，与大多数可接受的答案一样，会导致错误消息Invalid object name 't1'和Incorrect syntax near 'SELECT'. Expecting SET.

UPDATE [t1]
SELECT [t1].[Value] = COALESCE([t1].[Value],'') + [t2].[Value]
FROM @tbl1 AS [t1]
LEFT JOIN @tbl2 AS [t2] ON [t1].[Id] = [t2].[Id]

我对XML PATH的实验，基于其他Stack Overflow响应(如何将多行文本连接到SQL Server中的单个文本字符串)，也会产生语法错误或不正确的结果。

有人能提供正确的语法吗?

Answer 1

您必须对行进行分组，使用string_agg将值组合在一起，然后运行update:

select @@version;DECLARE @tbl1 TABLE ([Id] INT, [Value] VARCHAR(10))
INSERT INTO @tbl1 ([Id]) VALUES (1),(2),(3)
DECLARE @tbl2 TABLE ([Id] INT, [Value] VARCHAR(10))
INSERT INTO @tbl2 ([Id],[Value]) VALUES (1,'A'),(3,'B'),(1,'C'),(2,'D'),(2,'E'),(3,'F'),(1,'G')
;with grouped_data as (
select tbl1.Id, STRING_AGG(tbl2.[Value], '') as value_aggregated
from @tbl1 tbl1
inner join @tbl2 tbl2 on tbl1.Id=tbl2.Id
group by tbl1.id
)
update tbl1 set [Value]=value_aggregated
from @tbl1 tbl1
inner join grouped_data gd on gd.Id=tbl1.id
select * from @tbl1

你可以检查它在这个DB Fiddle上运行