my code trip

A 1 line script to concatenate all the values in a table’s column and output a single comma-separated value (CSV) string, using the all-powerful SQL Serve STUFF function (for removing the leading comma) with the FOR XML clause.

The code snippet below uses the Pubs sample database that comes free with MS SQL Server.

USE Pubs
GO

SELECT STUFF((SELECT ',' + au_fname FROM Authors FOR XML PATH('')),1, 1, '') AS Column2CSVString

The output of the code snippet is as follows.

Abraham,Reginald,Cheryl,Michel,Innes,Ann,Marjorie,Morningstar,Burt,Sheryl,Livia,Shiva,Charlene,Stearns,Heather,Michael,Sylvia,Albert,Anne,Meander,Dean,Dirk,Johnson,Akiko

To order the column values in ASC or DESC order, add the ORDER BY clause after the tablename as shown below.

USE Pubs
GO

SELECT STUFF((SELECT ',' + au_fname FROM Authors ORDER BY 1 ASC FOR XML PATH('')),1, 1, '') AS Column2CSVString

The output of the code snippet is as follows.

Abraham,Akiko,Albert,Ann,Anne,Burt,Charlene,Cheryl,Dean,Dirk,Heather,Innes,Johnson,Livia,Marjorie,Meander,Michael,Michel,Morningstar,Reginald,Sheryl,Shiva,Stearns,Sylvia

Finally, if you want to make this really generic and use dynamic SQL to pass in the column name and table name and delimiter, then let me know and I will post the code here.