How to Format Query Result as Comma Separated Values (CSV)

Sometimes, you will encounter situations where you will need to format your results as comma separated values.

In SQL Server 2000, you can use a loop or a cursor, or you can keep in simple by using a variable that keeps on concatenating the column values. Two ways to do it follow:

   1:
   2: -- method 1
   3: -- complicated/more cryptic way of getting comma separated values
   4: -- would not recommend this; shown just for comparison purposes
   5: USE AdventureWorksLT
   6: GO
   7: DECLARE @LastName        VARCHAR(100)
   8: DECLARE @LastNames    VARCHAR(MAX)
   9: SET @LastNames = ''
  10:
  11: DECLARE LastNameCursor CURSOR SCROLL STATIC READ_ONLY FOR
  12: SELECT LastName
  13: FROM SalesLT.Customer
  14:
  15: OPEN LastNameCursor
  16:
  17: FETCH NEXT FROM LastNameCursor
  18: INTO @LastName
  19: WHILE (@@FETCH_STATUS = 0) BEGIN
  20:
  21:        SET @LastNames = @LastNames + @LastName + ', '
  22:
  23:        FETCH NEXT FROM LastNameCursor
  24:        INTO @LastName
  25: END
  26:
  27: -- display
  28: SELECT SUBSTRING(@LastNames, 1, DATALENGTH(@LastNames) - 2)
  29:
  30: CLOSE LastNameCursor
  31: DEALLOCATE LastNameCursor
   1: -- method 2
   2: -- better way, less cryptic
   3: USE AdventureWorksLT
   4: GO
   5: DECLARE @LastNames VARCHAR(MAX)
   6: SET @LastNames = ''
   7: SELECT
   8:     @LastNames = @LastNames + ', ' + LastName
   9: FROM
  10:     SalesLT.Customer
  11:
  12: -- display; remove the trailing comma and space
  13: SELECT RIGHT(@LastNames, LEN(@LastNames) - 2)

In SQL Server 2005/2008, an additional alternative way is born that can be used to get the comma separated values – by using FOR XML PATH.

   1:
   2: -- method 3
   3: -- this gets all last names separated by commas
   4: -- issue is there is an extra comma and space after the last value
   5: USE AdventureWorksLT
   6: GO
   7: SELECT
   8:     CAST
   9:     (
  10:         (
  11:             SELECT LastName + ', '
  12:             FROM SalesLT.Customer
  13:             FOR XML PATH ('')
  14:         )
  15:         AS VARCHAR(MAX)
  16: )
  17:
  18: -- alternative way, still using FOR XML PATH, is to use the LEFT Transact-SQL string function
  19: -- in the example below, I'll use a variable to temporarily store the string
  20: -- otherwise the query will need a subquery, and it will be a little cryptic to read
  21: DECLARE @LastNames VARCHAR(MAX)
  22: SELECT
  23:     @LastNames =
  24:         CAST
  25:         (
  26:             (
  27:                 SELECT LastName + ', '
  28:                 FROM SalesLT.Customer
  29:                 FOR XML PATH ('')
  30:             )
  31:             AS VARCHAR(MAX)
  32:         )
  33: SELECT
  34:     LEFT(@LastNames, LEN(@LastNames) - 2)
Advertisements

4 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s