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)
VN:F [1.9.22_1171]
Rating: 7.1/10 (17 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
How to Format Query Result as Comma Separated Values (CSV), 7.1 out of 10 based on 17 ratings  
Be Sociable, Share!
  • Tweet