Search code examples
sqlsql-serverselectinner-join

SQL Server : one to many select query

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!

Solution

  • You can use FOR XML with SUFF for this:

    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;

    Results:

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