Concatenating Column Values into a Comma-Separated List

Question

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

User · Answer

You can do this using stuff   SELECT Stuff            SELECT        CARS CarName     FROM CARS     FOR XML PATH            1  2      AS CarNames

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT LEFT Car  LEN Car  - 1  FROM       SELECT Car            FROM Cars     FOR XML PATH          c  Car

User · Answer

DECLARE  SQL AS VARCHAR 8000  SELECT  SQL   ISNULL  SQL           ColumnName FROM TableName SELECT  SQL

User · Answer

Please try this with the following code   DECLARE  listStr VARCHAR MAX  SELECT  listStr   COALESCE  listStr             CarName FROM Cars SELECT  listStr

User · Answer

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

[sql-server] Concatenating Column Values into a Comma-Separated List

Examples related to sql-server

Examples related to tsql