Is it possible to get the SQL single column result as a comma separated string?

Question

Possible Duplicate:
How do I Create a Comma-Separated List using a SQL Query?

I am working on a web application. I need the SQL query of single column selection like

select 
  recordid 
from 
  device 
where 
  accountid in (1,2)) 

I need this result to be formatted comma separated string from SQL.

Answer 1

DECLARE @Concat varchar(max)

select @Concat = CAST(recordid as varchar(10)) + coalesce(',' + @Concat , '')
 from device 
 where accountid in (1,2)

 SELECT @Concat

Answer 2

You can use something like this:

DECLARE @result AS VARCHAR(MAX)

SET @result = '' -- initialize with empty string to avoid NULL result

SELECT
  @result = @result + ',' + CAST(recordid AS VARCHAR)
FROM
  device
WHERE
  accountid IN (1,2)


SELECT @result AS recordids

Answer 3

This is covered in detail in other questions, including:

• SQL Query to return values of a particular column concactenated with comma (,)
• How do I Create a Comma-Separated List using a SQL Query?

Answer 4

You can also write a custom CLR aggregate function which can end up being more optimized than using string concatenation (especially with a really large result set).