Using COALESCE function to make values separated with commas

by 
DECLARE @List VARCHAR(8000)

SELECT @List = COALESCE(@List + ',', '') + CAST(OfferID AS VARCHAR)
FROM   Emp
WHERE  EmpID = 23

SELECT @List

This approach to aggregate concatenation is not guaranteed to work. If you are on at least SQL Server 2005 XML PATH or CLR aggregates are preferred.

The definitive article on the subject is Concatenating Row Values in Transact-SQL

More Related Contents:

  1. incorrect syntax near keyword where [closed]
  2. nvarchar concatenation / index / nvarchar(max) inexplicable behavior
  3. How do I create a unique constraint that also allows nulls?
  4. How do I get list of all tables in a database using TSQL?
  5. Escape a string in SQL Server so that it is safe to use in LIKE expression
  6. SQL time difference between two dates result in hh:mm:ss
  7. Storing files in SQL Server
  8. Dapper.NET and stored proc with multiple result sets
  9. “order by newid()” – how does it work?
  10. Import ‘xml’ into Sql Server
  11. Query times out when executed from web, but super-fast when executed from SSMS
  12. T-SQL Dynamic SQL and Temp Tables
  13. Efficient ISNUMERIC() replacements on SQL Server?
  14. Login to Microsoft SQL Server Error: 18456
  15. Adding a query hint when calling Table-Valued Function
  16. Diagnosing Deadlocks in SQL Server 2005
  17. What is a simple command line program or script to backup SQL server databases?
  18. Disadvantages of MARS (Multiple Active Result Sets)?
  19. SQL Server Output Clause into a scalar variable
  20. Replace single quotes in SQL Server
  21. Why is sql server storing question mark characters instead of Japanese characters in NVarchar fields?
  22. sql use statement with variable
  23. How to create a yes/no boolean field in SQL server?
  24. How to display two digits after decimal point in SQL Server
  25. Copy table to a different database on a different SQL Server
  26. Include missing months in Group By query
  27. SQL SERVER – Understanding how MIN(text) works
  28. Pivot multiple columns based on one column in SQL Server
  29. How to convert text column to datetime in SQL
  30. How to calculate difference in hours (decimal) between two dates in SQL Server?

Leave a Comment