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.
[code='sql']
USE Pubs
GO
SELECT STUFF((SELECT ',' + au_fname FROM Authors FOR XML PATH('')),1, 1, '') AS Column2CSVString
[/code]
The output of the code snippet is as follows.
[code='html']
Abraham,Reginald,Cheryl,Michel,Innes,Ann,Marjorie,Morningstar,Burt,Sheryl,Livia,Shiva,Charlene,Stearns,Heather,Michael,Sylvia,Albert,Anne,Meander,Dean,Dirk,Johnson,Akiko
[/code]
To order the column values in ASC or DESC order, add the ORDER BY clause after the tablename as shown below.
[code='sql']
USE Pubs
GO
SELECT STUFF((SELECT ',' + au_fname FROM Authors ORDER BY 1 ASC FOR XML PATH('')),1, 1, '') AS Column2CSVString
[/code]
The output of the code snippet is as follows.
[code='html']
Abraham,Akiko,Albert,Ann,Anne,Burt,Charlene,Cheryl,Dean,Dirk,Heather,Innes,Johnson,Livia,Marjorie,Meander,Michael,Michel,Morningstar,Reginald,Sheryl,Shiva,Stearns,Sylvia
[/code]
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.
1546
Thanks! This worked like a charm
1547
Thanks man
I was searching for this a long time. I used the “function” workaround, but this is far more compact.
Thank you
1548
Just a quick note:
If your table is empty, the snippet above returns NULL, not an empty string.
Thanks for the code!
1549
It works but only with sql 2000 +
1552
Thank you!
1630
hi
thanks for your post.
I need your help in my project.
As you explained I have made a column as a comma separated string. Now my problem is how to attach this string to datalist.
suppose if the string equals dsc1.jpg, dsc2.jpg, dsc3.jpg i want to attach this to datalist so that I can see 3 photos in a datalist
thanks in advance
1640
@swapna. Take a look at this MSDN article. There are some source code snippets for the dataList also in the middle.
Hope that help…