How to Create a comma delimited sql result set

25 September,2012 by Jack Vamvas

Use the SQL COALESCE  function to create a comma delimited sql result set.

Normally , I disagree with  the SELECT statement managing client formatting issues .The application layer should deal with formatting the SELECT statement output to the client.

But ,sometimes, if there’s no application layer the SELECT is forced to manage formatting  issues. One example is producing a comma delimited result set from a sql statement. Usually a sql statement will produce a result set   , the result set is placed into a recordset , and depending on how the application sets the recordset properties , cursor behaviour is possible.

On this occasion , a developer requested I create a result set which returned one row , and that row containing a comma separated result set.

With a  COALESCE function combined with a CTE  , it is possible to create a comma delimited resultset

In this example, the CTE creates a temporary resultset of all databases on one drive with all databases that had a differential backup of more than 30 GB . The SELECT statement below , places the COALESCE results into the @CSVlist variable.


DECLARE @Quoted_Ident char(1); 
SET @Quoted_Ident = '';  
DECLARE @Delimeter char(1); 
SET @Delimeter = ',';
DECLARE @CSVlist nvarchar(max);
DECLARE @drive1 CHAR(1);
SET @drive1 = 'P';

WITH Results_CTE AS (

bus.database_name as 'dbname'
FROM msdb.dbo.backupset bus
INNER JOIN msdb.dbo.backupmediafamily bume ON bus.media_set_id = bume.media_set_id
bus.backup_start_date > GETDATE() - 7
and bus.[type] = 'I'
and    CAST(bus.backup_size /1024/1024/1024 AS DECIMAL(10,2))> 30.00
from sys.sysdatabases where LEFT(RTRIM(LTRIM(filename)),1) IN (@drive1)
SELECT @CSVlist = COALESCE(@CSVlist + @Delimeter, '') + @Quoted_Ident + dbname
FROM Results_CTE order by dbname 

select @CSVlist

Related Posts 


Export-CSV Powershell

SQL Server - Bulk Insert CSV into a SQL Server table


Author: Jack Vamvas (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on How to Create a comma delimited sql result set | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer