Simplest SQL Query for populating Comma separated results in SQL Server

Database T-SQL developers often face a scenario in which they need to write a SQL query to populate comma-separated results which can further be utilized as CSV formatted files as an input in some other processes. 

What is the Comma Separated file format?

Comma Separated Values or CSV file is a type of flat file in which data is distributed into multiple lines and each line has commas to separate all the fields. Apart from comma to separate columns, any CSV file can also have a Pipe '|', Tab, or any other character to separate columns or fields.

(Refer to below image of how CSV file content looks like)

Let's refer to the below SQL table to produce a result as CSV formated output- 

T-SQL Query: Select ID, URL FROM [dbo].[BlogPingWebSites]

The above table has two fields and producing below result.

But as per the requirement, the expected result is in CSV format rather than table view as below -

So, to populate the above CSV format, let's look into below T-SQL query -

-- Declare the variable @listStr to hold the output string

DECLARE @listStr VARCHAR(MAX)

-- Loop and assign the result string till the last row

-- Char(10) is used at last to insert a Line Feed

SELECT @listStr = ISNULL(@listStr ,'') + cast(ID AS VARCHAR) + ',' + URL + Char(10)

FROM [dbo].[BlogPingWebSites] 

SELECT @listStr

The above query will return the expected output into CSV format, and the field separator can also be replaced by Pipe '|' or any other sign instead of Comma.

Hope you would like this article.