Q:

How do I merge data from several rows in SQL Server

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.

A:

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 ;

