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
  • 797
  • 1
  • 7
  • 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
  • 419,657
  • 83
  • 708
  • 800
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
  • 10,830
  • 4
  • 44
  • 55
2

This is covered in detail in other questions, including:

Community
  • 1
  • 1
Bill Karwin
  • 499,602
  • 82
  • 638
  • 795
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,280
  • 24
  • 27