Merge multiple rows into single value using update Query

Question

Here is the scenario. I have two tables. I want to merge multiple row value to single value using update query.

   DECLARE @Table as Table
    (
        id int,
        name varchar(10)
    )

    insert into @Table values(1,'a')
    insert into @Table values(1,'b')
    insert into @Table values(1,'c')


    select * from @Table


    DECLARE @Table2 as Table
    (
        id int,
        name varchar(10)
    )

    insert into @Table2 values(1,'a')

    update t2 set name = t1.name from @Table2 t2
    inner join @Table t1 on t1.id=t2.id  

    select * from @Table2

I want output from @Table2 as by using update query

    id           name
   -----        --------
    1            a,b,c

possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — ASh, Mar 09 '15 at 18:45
You should never store data like that. It violates 1NF. If you want this type of thing for output it is another story. It has also been answered around here 4-5 every single day. — Sean Lange, Mar 09 '15 at 18:46
@ASh i want to concatenate using update statement and join operation i have seen your reference, but it is different from what i asked. — Nitin Aggarwal, Mar 09 '15 at 18:48
The heart of the question is identical. Applying the end result as an update does not change it. — shawnt00, Mar 09 '15 at 18:58
@Shawnt00 i have tried using similar answer but i wasn't getting the actual result, thanks anyways. — Nitin Aggarwal, Mar 09 '15 at 19:03

score 1 · Accepted Answer · answered Mar 09 '15 at 18:53

;WITH Table1 AS (
SELECT t.id 
      , STUFF((SELECT ',' +  name 
               FROM @Table 
               WHERE id = t.id
               FOR XML PATH(''), TYPE)
               .value('.','NVARCHAR(MAX)'),1,1,'') AS name
FROM @Table t
GROUP BY t.id)
UPDATE t2
 SET t2.name = t1.name 
FROM @Table2 t2 
INNER JOIN Table1 t1  ON t1.id=t2.id

Merge multiple rows into single value using update Query

1 Answers1