SQL Server convert select a column and convert it to a string

Question

Is it possible to write a statement that selects a column from a table and converts the results to a string?

Ideally I would want to have comma separated values.

For example, say that the SELECT statement looks something like

SELECT column FROM table WHERE column<10 

and the result is a column with values

|column| -------- |  1   | |  3   | |  5   | |  9   | 

I want as a result the string "1, 3, 5, 9"

Answer 1

You can do it like this:

Fiddle demo

declare @results varchar(500)  select @results = coalesce(@results + ',', '') +  convert(varchar(12),col) from t order by col  select @results as results  | RESULTS | ----------- | 1,3,5,9 | 

Answer 2

There is new method in SQL Server 2017:

SELECT STRING_AGG (column, ',') AS column FROM Table;

that will produce 1,3,5,9 for you