How to populate a column in a table with a string of concatenated values from a column in another ta-CodePudding

I'm trying to populate a column in a table with a string of concatenated values from a column in another table. There are numerous solutions suggested, such as How to concatenate text from multiple rows into a single text string in SQL Server, which has 47 answers, but none of them are working for me.

Table @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

Table @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

I'm seeking the syntax to update the records in table @tbl1 to this:

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

This doesn't work:

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

Result:

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

This syntax produces the same result:

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]

Changing SET to SELECT (below), as in most of the accepted answers, results in the error messages Invalid object name 't1' and 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]

My experiments with XML PATH, based upon other Stack Overflow responses (How to concatenate text from multiple rows into a single text string in SQL Server), also produce syntax errors or incorrect results.

Can someone offer the correct syntax?

CodePudding user response：

You have to group the rows, use string_agg to get the values together, and then run the 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

You can check it running on this DB Fiddle