如何合并SQL Server中多行的数据

Here is my situation:

这是我的情况:

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

TABLE PEOPLE(代码,名称,+具有相同代码的记录相同的其他字段)

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)

VIEW PEOPLE(代码,名称,+具有相同代码的记录相同的其他字段)

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".

有人可以帮我创建一个可以给我这个结果的视图吗?关键在于将行与相同的“代码”合并,并在“name”列中合并名称。对于具有相同“代码”的行,所有其他字段100%相同。

Thank you.

1 个解决方案

#1

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 ;