Skip to main content

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

6
  • 13
    This will work well with this data, but if your data might have xml special characters (e.g. <, >, &) they will be replaced (<, etc.) Commented Oct 12, 2008 at 0:39
  • 4
    @James You could use a CTE to accomplish this: WITH MyCTE(VehicleId, Name, Locations) AS ( SELECT [VehicleID] , [Name] , (SELECT CAST(City + ', ' AS VARCHAR(MAX)) FROM [Location] WHERE (VehicleID = Vehicle.VehicleID) FOR XML PATH ('') ) AS Locations FROM [Vehicle] ) SELECT VehicleId, Name, REPLACE(Locations, ',', CHAR(10)) AS Locations FROM MyCTE Commented May 24, 2011 at 15:27
  • 1
    i became a little confused whilst modifiny this code, so have now posted my own question Commented May 24, 2011 at 16:14
  • 1
    You can wrap the subquery in the STUFF function to get rid of the comma. Just have the query lead with ', ' and then wrap the subquery in: STUFF( subquery,1,2,'') Commented Dec 20, 2013 at 4:06
  • The line break characters can be inserted like this: 'some text' + CHAR(13) + CHAR(10) + 'text on next line'. Commented Feb 16, 2016 at 17:20