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 (

SELECT 
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
WHERE 
bus.backup_start_date > GETDATE() - 7
and bus.[type] = 'I'
and    CAST(bus.backup_size /1024/1024/1024 AS DECIMAL(10,2))> 30.00
UNION 
select 
name
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 

SQL Server CTE and PARTITION BY

Export-CSV Powershell

SQL Server - Bulk Insert CSV into a SQL Server table

!


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment on How to Create a comma delimited sql result set


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer