concatenating string

Question

Is there a way in SQL sever that can write the output as follow:

select events
  from mytable

original output

events
--------
123456
894531
985233
829292
920202
392939
299223

desired output

'123456', '894531','985233','829292','920202','392939','299223'

select '' + CustomerID + ',' from dbo.Customers customerid ALFKI, ANATR, ANTON, AROUT, BERGS,

Would like to see the result as customerid 'ALFKI', 'ANATR', 'ANTON', 'AROUT', 'BERGS', so on...

Answer 1

SELECT
  STUFF(
    (SELECT
      ', ' + events
     FROM dbo.mytable
     FOR XML PATH('')
    ), 1, 1, '') As concatenated_string

If you want the values enclosed in single quotes then edit the padding above.

Answer 2

Concatenating Row Values in Transact-SQL discusses the various options you have, like recursive CTE, blackbox XML (like Mitch' solution), CLR, cursors, recursive scalar UDFs etc etc, as well as some unreliable but fast approaches (SELECT with variable concatenation).