How to convert Result of SELECT Command to Comma Separated String in SQL Server? Example

Sometimes, you need the result of the 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 editplus to replace the \n to comma (,) to create such CSV string because it supports regular expression-based find and replaces 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 a data drive application.

One reason 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 the power to do the stuff like that.

SQL Command to Convert Result of SELECT clause to CSV String

In order to collect the 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 a 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 the result of the 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 that 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.

Other SQL Articles and Resources You May Like:

• Top 5 Courses to learn SQL and Database Fundamentals
• Top 5 Courses to learn Microsoft SQL Server
• Top 5 Courses to learn MySQL Database
• Top 5 Books for Database Design and Modeling
• The Frontend and Backend Developer RoadMap
• Top 4 Books to learn Oracle and PL/SQL
• 10 Things Every Programmer Should Learn 
• Top 5 Advanced SQL Books for Programmers 
• 5 Free T-SQL and SQL Server Courses for Programmers and DBAs
• 11 Things Java Developer Should learn
• Top 5 Courses to learn PostgreSQL for Beginners