PrismitaLEARN · PRACTICE · GROW

How do you concatenate row values into a single string in SQL Server?

SQL Server T-SQL Intermediate 2–3 Yrs3–5 Yrs SQL Query 1 min read

Quick Answer

Use STRING_AGG(column, ', ') (SQL Server 2017+) — the modern, readable way, with an optional WITHIN GROUP (ORDER BY ...). On older versions you use the STUFF(... FOR XML PATH('')) trick to build a delimited list.

On SQL Server 2017 or later it's just STRING_AGG(column, ', '), and you can order the items with WITHIN GROUP (ORDER BY ...). Before 2017 there was no built-in aggregate for this, so the standard trick was FOR XML PATH('') to concatenate the rows and STUFF to strip the leading separator — it works but it's ugly and you have to be careful with characters like ampersands that XML escapes. It's the SQL Server version of GROUP_CONCAT.

  • Modern: SELECT CustomerId, STRING_AGG(ProductName, ', ') WITHIN GROUP (ORDER BY ProductName) AS Products FROM OrderLines GROUP BY CustomerId;
  • Legacy (pre-2017): STUFF((SELECT ', ' + ProductName FROM OrderLines l WHERE l.CustomerId = c.CustomerId FOR XML PATH('')), 1, 2, '') — the STUFF removes the leading delimiter; use FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)') to handle special characters like &.
  • This is the SQL Server equivalent of MySQL's GROUP_CONCAT or Postgres's string_agg.
All SQL Server questions More on T-SQL