The way to convert Results of SELECT Command to Comma Separated String in SQL Server? Instance

Typically, you want the results of the SQL SELECT clause as a comma-separated String e.g. if you’re outputting ids of white-listed merchandise. By default, the SELECT command will print every row in a single line and also you get a column of names or ids. Should you want a comma-separated String then you definitely in all probability want to make use of a textual content editor like Notepad++, Edit Plus, or a instrument like Microsoft Excel to transform that column of values into an enormous CSV String. I usually use a textual content editor like editplus to interchange the n to comma (,) to create such CSV string as a result of it helps common expression-based discover and replaces operation.

Out of the blue, a thought got here to my thoughts, why not do that in T-SQL within the SQL Server. It will not solely save the time but in addition provide you with choices like create a sorted record of comma-separated String utilizing order by clause. It is a good trick and helpful in lots of eventualities, particularly if you’re working with a knowledge drive software.

One motive why I could not consider this resolution earlier than was my lack of information in T-SQL. As I study extra and get snug with T-SQL, I noticed it is the ability to do the stuff like that.

SQL Command to Convert Results of SELECT clause to CSV String

With the intention to gather the results of SELECT clause in a comma-separated String, you want to use a VARCHAR variable and concatenate every row by utilizing that variable in SELECT clause utilizing the COALESCE expression as proven beneath:

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

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

This can be a very nice tip and it had helped me loads and, after all, saves time and effort too. You should use this everytime you want a comma-separated record of values from a desk. It is notably helpful once you output only one worth from a desk e.g. record of supported ids.

Right here is yet one more instance of making a comma-separated String from the results of a SELECT clause in SQL, right here we needed names of all buyer as CSV String:

As I mentioned earlier than, you’ll be able to change the delimiter from comma to colon, pipe, or anything as properly, simply put what delimiter you need within the COALESCE expression. Right here is an instance of changing the results of the SELECT command to colon-separated String:

That is all about find out how to convert the results of a SELECT command to comma separated String in SQL Server utilizing T-SQL. Primarily based upon this method you can too give you code for Oracle utilizing PL/SQL and one other database that permits variables.

As soon as you recognize the trick there is no such thing as a must do double work of getting information from the database after which utilizing a textual content editor to transform that record of values into an enormous CSV string. You can too change the delimiter and get an inventory of colon-separated worth or pipe delimited values.