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.

One reason of why I couldn't think of this solution before was my lack of knowledge in T-SQL. As I learn more and get comfortable with T-SQL, I realized it's power to do the stuff like that.

One book which really helped me to understand Microsoft SQL Server and T-SQL better is the Microsoft SQL Server 2012 T-SQL Fundamentals, one of the best books to learn T-SQL.

SQL Command to Convert Result of SELECT clause to CSV String

In order to collect result of SELECT clause in a comma separated String, you need to use a VARCHAR variable and concatenate each row by using that variable in SELECT clause using the COALESCE expression as shown below:

DECLARE @csv VARCHAR(MAX)
SELECT @csv = COALESCE(@csv + ',' ,'') + book_title from Test.dbo.Books where book_id IN ( 101, 102, 103);
SELECT @csv 

Output
Head First SQL, SQL Puzzler, T-SQL Fundamentals

This is a really nice tip and it had helped me a lot and, of course, saves effort and time too. You can use this whenever you need a comma separated list of values from a table. It's particularly useful when you output just one value from a table e.g. list of supported ids.

Here is one more example of creating a comma separated String from the result of SELECT clause in SQL, here we wanted names of all customer as CSV String.

As I said before, you can change the delimiter from comma to colon, pipe or anything else as well, just put what delimiter you want in the COALESCE expression. Here is an example of converting result of SELECT command to colon separated String.

That's all about how to convert the result of a SELECT command to comma separated String in SQL Server using T-SQL. Based upon this technique you can also come up with code for Oracle using PL/SQL and another database which allows variables. Once you know the trick there is no need to do double work of getting data from the database and then using a text editor to convert that list of values into a big CSV string. You can also change the delimiter and get a list of colon separated value or pipe delimited values.