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.
DECLARE @Concat varchar(max)
select @Concat = CAST(recordid as varchar(10)) + coalesce(',' + @Concat , '')
from device
where accountid in (1,2)
SELECT @Concat
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
This is covered in detail in other questions, including:
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).
