CODEWITHSUNDEEP
sqlsql-servert-sqlstring-concatenation
Chris
Chris

Reputation: 39

concatenating string

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

Upvotes: 3

Views: 473

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294177

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

Upvotes: 3

Mitch Wheat
Mitch Wheat

Reputation: 300489

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.

Upvotes: 5

Related Questions