Return to Answer

If you're using SQL Server 2005, you could use the FOR XML PATH command.

SELECT [VehicleID]
     , [Name]
     , (STUFF((SELECT CAST(', ' + [City] AS VARCHAR(MAX)) 
         FROM [Location] 
         WHERE (VehicleID = Vehicle.VehicleID) 
         FOR XML PATH ('')), 1, 2, '')) AS Locations
FROM [Vehicle]

It's a lot easier than using a cursor, and seems to work fairly well.

If you're using SQL Server 2005, you could use the FOR XML PATH command.

SELECT [VehicleID]
     , [Name]
     , (STUFF((SELECT CAST(', ' + [City] AS VARCHAR(MAX)) 
         FROM [Location] 
         WHERE (VehicleID = Vehicle.VehicleID) 
         FOR XML PATH ('')), 1, 2, '')) AS Locations
FROM [Vehicle]

It's a lot easier than using a cursor, and seems to work fairly well.

Update

For anyone still using this method with newer versions of SQL Server, there is another way of doing it which is a bit easier and more performant using the STRING_AGG method that has been available since SQL Server 2017.

SELECT  [VehicleID]
       ,[Name]
       ,(SELECT STRING_AGG([City], ', ')
         FROM [Location]
         WHERE VehicleID = V.VehicleID) AS Locations
FROM   [Vehicle] V

This also allows a different separator to be specified as the second parameter, providing a little more flexibility over the former method.

If you're using SQL Server 2005, you could use the FOR XML PATH command.

SELECT [VehicleID]
     , [Name]
     , (SELECT CAST(City + ', ' AS VARCHAR(MAX)) 
         FROM [Location] 
         WHERE (VehicleID = Vehicle.VehicleID) 
         FOR XML PATH ('')
      ) AS Locations
FROM [Vehicle]

It's a lot easier than using a cursor, and seems to work fairly well.

If you're using SQL Server 2005, you could use the FOR XML PATH command.

SELECT [VehicleID]
     , [Name]
     , (STUFF((SELECT CAST(', ' + [City] AS VARCHAR(MAX)) 
         FROM [Location] 
         WHERE (VehicleID = Vehicle.VehicleID) 
         FOR XML PATH ('')), 1, 2, '')) AS Locations
FROM [Vehicle]

It's a lot easier than using a cursor, and seems to work fairly well.

If you're using SQL Server 2005, you could use the FOR XML PATH command.

SELECT [VehicleID]
     , [Name]
     , (SELECT CAST(City + ', ' AS VARCHAR(MAX)) FROM [Location] WHERE (VehicleID = Vehicle.VehicleID) FOR XML PATH ('')) AS Locations
FROM [Vehicle]

It's a lot easier than using a cursor, and seems to work fairly well.

If you're using SQL Server 2005, you could use the FOR XML PATH command.

SELECT [VehicleID]
     , [Name]
     , (SELECT CAST(City + ', ' AS VARCHAR(MAX)) 
         FROM [Location] 
         WHERE (VehicleID = Vehicle.VehicleID) 
         FOR XML PATH ('')
      ) AS Locations
FROM [Vehicle]

It's a lot easier than using a cursor, and seems to work fairly well.