Create a Comma Separated List from Column Using Select Statement

Estimated study time: 1 minute. Roll up rows into one comma-separated string.

Sometimes you don't want one row per value — you want every value from a column squashed into a single, comma-separated string. SQL Server's STRING_AGG function does exactly this.

Basic Syntax

SELECT STRING_AGG(ColumnName, ', ') AS CombinedList
FROM TableName;

Example

SELECT STRING_AGG(EmpName, ', ') AS AllEmployees
FROM Employees;

This returns a single row containing every employee name, separated by a comma and a space.

Grouping the List

Combine STRING_AGG with GROUP BY to get one comma-separated list per group instead of one for the whole table:

SELECT DeptID, STRING_AGG(EmpName, ', ') AS EmployeesInDept
FROM Employees
GROUP BY DeptID;

Ordering Values Inside the List

Use WITHIN GROUP to control the order the values appear in:

SELECT STRING_AGG(EmpName, ', ') WITHIN GROUP (ORDER BY EmpName)
FROM Employees;