0

I am trying to find the best way to consolidate the string in the fields that have the same value. Please look at the example below:

Table1:

Column1 | Column2 | Column3 | Column4
test1     test2     test3     string1
test1     test2     test3     string2
test4     test5     test6     string3
test4     test5     test6     string4

The result need to be inserted to Table2 like following:

Table2:

Column1 | Column2 | Column3 | Column4
test1     test2     test3     string1 string2
test4     test5     test6     string3 string4

I need to avoid Cursors please. Thank you. Please note: I can not use the XML PATH.

The following query wouldn't work:

SELECT
      m.Column1,m.Column2,m.Column3
    , Column4 = STUFF((
          SELECT ' ' + Column4
          FROM dbo.Table1
          FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
FROM dbo.Table1 m
Improve this question
15
  • Do you have only two rows that match? Commented Nov 20, 2018 at 21:33
  • 1
    Investigate "group by" on the first three columns, and the use of STUFF... check out this answer: stackoverflow.com/questions/13647394/… Commented Nov 20, 2018 at 21:35
  • just another similar answer stackoverflow.com/questions/18910134/… Commented Nov 20, 2018 at 21:36
  • Similar @morsik but for SQL Server they'll need this one: stackoverflow.com/questions/17591490/… which is mentioned in your post but as the third answer. MySQL was accepted. Commented Nov 20, 2018 at 21:37
  • 1
    And the list of dupes can get even bigger... Commented Nov 20, 2018 at 21:41
Related questions
2451
How to concatenate text from multiple rows into a single text string in SQL Server
4339
How do I UPDATE from a SELECT in SQL Server?
3327
How to add a column with a default value to an existing table in SQL Server?
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.