How to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field? [duplicate]

by

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.

More Related Contents:

  1. How to concatenate text from multiple rows into a single text string in SQL Server
  2. How to use GROUP BY to concatenate strings in SQL Server?
  3. SQL NVARCHAR and VARCHAR Limits
  4. Concat groups in SQL Server [duplicate]
  5. select yesterday date in where condition
  6. SQL Server dynamic PIVOT query?
  7. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  8. SQL query to select dates between two dates
  9. What is the benefit of using “SET XACT_ABORT ON” in a stored procedure?
  10. is there an advantage to varchar(500) over varchar(8000)?
  11. What is best tool to compare two SQL Server databases (schema and data)? [duplicate]
  12. SQL query to split column data into rows
  13. Calculating number of full months between two dates in SQL
  14. Replacing NULL with 0 in a SQL server query
  15. How do I calculate a running total in SQL without using a cursor?
  16. Equivalent of Oracle’s RowID in SQL Server
  17. Months between two dates
  18. Dynamic update statement with variable column names
  19. Why can’t I use alias in a count(*) “column” and reference it in a having clause?
  20. A way to extract from a DateTime value data without seconds
  21. SQL Server (TSQL) – Is it possible to EXEC statements in parallel?
  22. How to find third or nᵗʰ maximum salary from salary table?
  23. SQL “between” not inclusive
  24. Difference of two date time in sql server
  25. Using PIVOT in SQL Server 2008
  26. sql group by only rows which are in sequence
  27. Does UNION ALL guarantee the order of the result set [duplicate]
  28. How to find sum of multiple columns in a table in SQL Server 2005?
  29. Query times out from web app but runs fine from management studio
  30. Can’t create stored procedure with table output parameter

Leave a Comment