How to Convert Result of SELECT Command to Comma Separated String in SQL Server

Sometimes, you need the result of SQL SELECT clause as a comma separated String e.g. if you are outputting ids of white-listed products. By default, the SELECT command will print each row in one line and you get a column of names or ids. If you need a comma separated String then you probably need to use a text editor like Notepad++, Edit Plus, or a tool like Microsoft Excel to convert that column of values into a big CSV String. I often use a text editor like edit plus to replace the \n to comma (,) to create such CSV string because it support regular expression based find and replace operation. Suddenly, a thought came to my mind, why not do this in T-SQL in the SQL Server. It would not only save the time but also give you options like create a sorted list of comma separated String using order by clause. It's a nice trick and useful in many scenarios especially if you are working with data drive application.

Read more »