How do I merge data from several rows in SQL Server

Question

Here is my situation:

TABLE PEOPLE (code, name, + other fields that are identical for records with same code)

1;John Wayne
2;Jack Smith
2;Jill Smith
3;Bill Peyton
3;Gill Peyton
3;Billy Peyton

The result I would like:

VIEW PEOPLE (code, name, + other fields that are identical for records with same code)

1;John Wayne
2;Jack Smith Jill Smith
3;Bill Peyton Jill Peyton Billy Peyton

Can some one please help me create a view that would give me this result? The point is merging rows with same "code" and merge names in column "name". All the other fields are 100% identical for rows with same "code".

Thank you.

This has been asked so many times. Do a quick search with the tags sql-server and group-concat — Lamak, Jun 08 '12 at 13:26
possible duplicate of [Mimic group_concat() combined with group](http://stackoverflow.com/questions/10298805/mimic-group-concat-combined-with-group) — Lamak, Jun 08 '12 at 13:27
possible duplicate of [How to return multiple values in one column (T-SQL)?](http://stackoverflow.com/questions/122942/how-to-return-multiple-values-in-one-column-t-sql) — Alex K., Jun 08 '12 at 13:30
possible duplicate of [SQL Server - Possible Pivot Solution?](http://stackoverflow.com/questions/10791247/sql-server-possible-pivot-solution) — GarethD, Jun 08 '12 at 13:33
Check this link http://explainextended.com/2010/06/21/group_concat-in-sql-server/ In mySQL exists a function that do exactly what you want, but in sqlServer it's more difficult.. you have to "emulate" that function — Gonzalo.-, Jun 08 '12 at 13:33
thanks to all that tried to help. Sorry for the duplicate i was not aware of group_concat. All i had was a problem :) — no9, Jun 08 '12 at 13:45

score 3 · Accepted Answer · edited Jun 08 '12 at 13:34

3

Try this

SELECT Code,

       ( SELECT Name + ' '

           FROM Table1 t2

          WHERE t2.Code = t1.Code

          ORDER BY Name

            FOR XML PATH('') ) AS Name

      FROM Table1 t1

      GROUP BY Code ;

edited Jun 08 '12 at 13:34

GarethD

68,045
10
83
123

answered Jun 08 '12 at 13:26

rs.

26,707
12
68
90

This works up until there are special xml characters in the name. You should use `.value` in conjunction with the `XML PATH` method to avoid these errors. http://stackoverflow.com/questions/5031204/does-t-sql-have-an-aggregate-function-to-concatenate-strings/5031297#5031297 – GarethD Jun 08 '12 at 13:37
Thank you very much!!! May you hit the jackpot and get laid several times on this loveely friday ;) – no9 Jun 08 '12 at 13:43

How do I merge data from several rows in SQL Server

1 Answers1