SQL Server : one to many select query

Question

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!

score 3 · Accepted Answer · 2017-10-22T12:36:44.113

3

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;

Demo

Results:

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

edited Oct 22 '17 at 12:36

answered Oct 22 '17 at 12:33

@GordonLinoff Thanks, yes you are right. – Oct 22 '17 at 12:36
thank you, it works fine. How can this been update in order to exclude those Persons that not have vehicles? Because so far I am getting all Persons with null on Vehicles(if they don't own vehicles).Thank you. – Oct 22 '17 at 12:47

score 2 · Answer 2 · answered Oct 22 '17 at 12:37

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

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.

thank you but unfortunately I am using an older version of SQL Server. — , Oct 22 '17 at 12:45

SQL Server : one to many select query

2 Answers2