1

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.

Community
  • 1
  • 1
Dharma
  • 837
  • 1
  • 9
  • 14

4 Answers4

5
DECLARE @Concat varchar(max)

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

 SELECT @Concat
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
3

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
Grzegorz Gierlik
  • 11,112
  • 4
  • 47
  • 55
2

This is covered in detail in other questions, including:

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

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).

CMerat
  • 4,398
  • 25
  • 28