T-SQL 连接 &将多行分组为一行

问题描述

我正在寻找一种分组方式，同时还将行连接成逗号分隔的字符串.示例:

I'm looking for a way to group whilst also concatenating rows into a comma separated string. Example:

Name            Place
Steve Jones     New York
Steve Jones     Washington
Albert Smith    Miami
Albert Smith    Denver

到...

Steve Jones    New York, Washington
Albert Smith   Miami, Denver

非常感谢

推荐答案

如果您使用 SQL Server 2008 及更高版本，您可以使用 STUFF 和 XML PATH 来获得您想要的结果.

If you use SQL Server 2008 and above, you can use STUFF and XML PATH to get the result you want.

SELECT DISTINCT Name
    , STUFF((
        SELECT ',' + Place
        FROM YourTable t1
        where t1.Name = t2.Name
        FOR XML PATH('')
    ), 1, 1, '') AS Places
FROM YourTable t2