SQL Query to Concatenate text

Asked Nov 20 '18 at 21:33

Active Nov 20 '18 at 22:49

Viewed 82 times

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

edited Nov 20 '18 at 22:49

asked Nov 20 '18 at 21:33

user3314399

Do you have only two rows that match? – Gordon Linoff Nov 20 '18 at 21:33
1

Investigate "group by" on the first three columns, and the use of STUFF... check out this answer: https://stackoverflow.com/questions/13647394/concatenate-one-field-after-group-by – pmbAustin Nov 20 '18 at 21:35
just another similar answer https://stackoverflow.com/questions/18910134/group-by-but-get-all-values-from-other-column – morsik Nov 20 '18 at 21:36
Similar @morsik but for SQL Server they'll need this one: https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server which is mentioned in your post but as the third answer. MySQL was accepted. – S3S Nov 20 '18 at 21:37
@gordonLinoff no it can be up to 4 rows at the time that match. – user3314399 Nov 20 '18 at 21:38
1

And the list of dupes can get even bigger... – Zohar Peled Nov 20 '18 at 21:41
@pmbAustin can you please provide an example? – user3314399 Nov 20 '18 at 21:45
I've listed 4 different duplicates. Surly you can find your answer in at least one of them... – Zohar Peled Nov 20 '18 at 21:47
Thank you. But I cant use XML Path since the data sometimes includes characters that can not be serialized. – user3314399 Nov 20 '18 at 22:11
@zoharpeled shall I ask the question again? – user3314399 Nov 20 '18 at 22:26
Have you tried `(select .... for xml path, type).value('.', 'varchar(max)')`? – Zohar Peled Nov 20 '18 at 22:41
Yes, still the same error. – user3314399 Nov 20 '18 at 22:43
If you could edit the question to include an example that you can't use `for xml` with, then I (or someone else) will probably re-open it. – Zohar Peled Nov 20 '18 at 22:44
done. Please re-open the question. – user3314399 Nov 20 '18 at 22:50
Show some sample data that failed... – Zohar Peled Nov 21 '18 at 04:32

SQL Query to Concatenate text

0 Answers0