Creating Comma Delimited List (CSV) directly from SQL

This post serves as a self-reminder of how to perform SQL Records to CSV using Ad-Hoc SQL Query.

Getting data in CSV format is one of the features I keep using from time to time, and instead of searching for the same solution over Goggle over and over again, I created this post just to remind myself and serve as a note reference.

The following approach is derived from this discussion on stackoverflow.
http://stackoverflow.com/questions/1564980/building-a-comma-separated-list

Disclaimer: This approach is for MS SQL Server 2008 or later. The syntax probably won't work on other Relationsional Database Management System (RDBMS).

Let say we have a table like this:

If we want to retrieve the id column in comma delimited format.

SELECT STUFF((
    SELECT ', ' + cast(id as varchar(max))
    FROM [dbo].[username]
    FOR XML PATH('')
    ), 1, 2, '') AS CSV_Id;

If we want to retrieve the name column in comma delimited format, together with text quantifier of a single quote:

SELECT STUFF((

    SELECT ', ''' + cast(name as varchar(max)) + ''''

    FROM [dbo].[username]

    FOR XML PATH('')

    ), 1, 2, '') AS CSV_Name;