SQL Server：一对多选择查询[重复]

This question already has an answer here:

这个问题在这里已有答案：

Simulating group_concat MySQL function in Microsoft SQL Server 2005? 9 answers
在Microsoft SQL Server 2005中模拟group_concat MySQL函数？ 9个答案

I have two tables, logically related one to many.

我有两个表，逻辑上相关的一对多。

First table:

第一张表：

CREATE TABLE Persons 
(
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255), 
    Age int
);

Second table:

第二表：

CREATE TABLE Vehicles 
(
    Brand varchar(50), 
    PersonID int,

    FOREIGN KEY(PersonID) REFERENCES Persons(ID)
);

My approach is to list each of the Persons and the vehicles that this Person own.

我的方法是列出每个人和这个人拥有的车辆。

What I managed to do:

我做了什么：

SELECT LastName, brand 
FROM vehicles
INNER JOIN Persons ON Persons.ID = PersonID
ORDER BY LastName ASC

Unfortunately this is not what I have in mind to do, which is to display every Person once and next to it a list of vehicles for ex.:

不幸的是，这不是我想要做的，这是为了显示每个人一次和旁边的车辆清单：

User1 | kia, ford, jeep
User2 | ferrari, harley

Is it doable and if so what is the appropriate way? Any help is welcome!

它是否可行，如果是，那么适当的方式是什么？欢迎任何帮助！

2 个解决方案

#1

You can use FOR XML with SUFF for this:

您可以将FOR XML与SUFF一起使用：

SELECT FirstName, LastName,
STUFF((
    SELECT ', ' + v.Brand
    FROM vehicles as v
    WHERE v.PersonID = p.ID
    FOR XML PATH (''))
  ,1,2,'') AS Vehicles
FROM Persons AS p;

Demo
演示

Results:

结果：

| FirstName | LastName |        Vehicles |
|-----------|----------|-----------------|
|     User1 |    user1 | Ford, kia, jeep |
|     User2 |    User2 |  ferrri, harley |

#2

The most recent version of SQL Server (SQL Server 2017) finally has this functionality built-in. So you can do:

最新版本的SQL Server（SQL Server 2017）最终内置了此功能。所以你可以这样做：

SELECT p.LastName,
       STRING_AGG(v.brand, ', ') WITHIN GROUP (ORDER BY v.brand) as vehicles
FROM Persons p JOIN
     vehicles v
     ON p.ID = v.PersonID
GROUP BY p.ID, p.LastName;

When you have a query with more than one table, I would advise you to always use table aliases and qualified column names.

如果您有一个包含多个表的查询，我建议您始终使用表别名和限定列名。

If you want people without vehicles to be included, then use a LEFT JOIN.

如果您希望包含没有车辆的人，请使用LEFT JOIN。

#1