Concatenating Column Values into a Comma-Separated List
Sql ServerTsqlSql Server Problem Overview
What is the TSQL syntax to format my output so that the column values appear as a string, seperated by commas.
Example, my table CARS has the following:
CarID CarName
----------------
1 Porsche
2 Mercedes
3 Ferrari
How do I get the car names as : Porsche, Mercedes, Ferrari
Sql Server Solutions
Solution 1 - Sql Server
SELECT LEFT(Car, LEN(Car) - 1)
FROM (
SELECT Car + ', '
FROM Cars
FOR XML PATH ('')
) c (Car)
Solution 2 - Sql Server
You can do a shortcut using coalesce to concatenate a series of strings from a record in a table, for example.
declare @aa varchar (200)
set @aa = ''
select @aa =
case when @aa = ''
then CarName
else @aa + coalesce(',' + CarName, '')
end
from Cars
print @aa
Solution 3 - Sql Server
You can do this using stuff:
SELECT Stuff(
(
SELECT ', ' + CARS.CarName
FROM CARS
FOR XML PATH('')
), 1, 2, '') AS CarNames
Solution 4 - Sql Server
If you are running on SQL Server 2017 or Azure SQL Database you do something like this :
SELECT STRING_AGG(CarName,',') as CarNames
FROM CARS
Solution 5 - Sql Server
DECLARE @CarList nvarchar(max);
SET @CarList = N'';
SELECT @CarList+=CarName+N','
FROM dbo.CARS;
SELECT LEFT(@CarList,LEN(@CarList)-1);
Thanks are due to whoever on SO showed me the use of accumulating data during a query.
Solution 6 - Sql Server
Another solution within a query :
select
Id,
STUFF(
(select (', "' + od.ProductName + '"')
from OrderDetails od (nolock)
where od.Order_Id = o.Id
order by od.ProductName
FOR XML PATH('')), 1, 2, ''
) ProductNames
from Orders o (nolock)
where o.Customer_Id = 525188
order by o.Id desc
(EDIT: thanks @user007 for the STUFF declaration)
Solution 7 - Sql Server
DECLARE @SQL AS VARCHAR(8000)
SELECT @SQL = ISNULL(@SQL+',','') + ColumnName FROM TableName
SELECT @SQL
Solution 8 - Sql Server
Please try this with the following code:
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + CarName
FROM Cars
SELECT @listStr