Jose Guay

Hi all.

I was trying to create a comma separated list of values out of a table in SQL Server but I was looking for a solution without cursors, and came across this solution in my research. I know, this probably is not something new but it was new to me, so I though I’d share it.

The following sample creates a comma separated list of cities from the addresses table in the AdventureWorks database.

Now, the idea is to create a function that will return the list that in the end, is a string, this way you can include it in your queries. Here is the code:

   1:  USE AdventureWorks

   2:  GO

   3:   

   4:  CREATE FUNCTION fnCSVList(@IDStart int, @IDEnd int)

   5:  RETURNS VARCHAR(2048)

   6:  AS

   7:  BEGIN

   8:      DECLARE @ResultString varchar(2048)

   9:      SELECT @ResultString = IsNull(@ResultString, '') + [City] + ','

  10:        FROM (SELECT DISTINCT [City] FROM Person.Address) as NewTable

  11:       WHERE [AddressID] BETWEEN @IDStart AND @IDEnd

  12:   

  13:      IF LEN(@ResultString) > 0

  14:          SET @ResultString = LEFT(@ResultString, LEN(@ResultString) - 1)

  15:   

  16:      RETURN @ResultString

  17:  END

Now, while this code works wonderful in SQL Server 2005, it doesn’t work in 2008. You know why? Because if you notice, the derivated table “NewTable” does not include the column AddressID, so in order to make it work, we only have to include it and move the where clause to NewTable the like this:

   1:  USE AdventureWorks

   2:  GO

   3:   

   4:  ALTER FUNCTION fnCSVList(@IDStart int, @IDEnd int)

   5:  RETURNS VARCHAR(2048)

   6:  AS

   7:  BEGIN

   8:      DECLARE @ResultString varchar(2048)

   9:      SELECT @ResultString = IsNull(@ResultString, '') + [City] + ','

  10:        FROM (SELECT DISTINCT [City] FROM Person.Address WHERE [AddressID] BETWEEN @IDStart AND @IDEnd) as NewTable

  11:       

  12:   

  13:      IF LEN(@ResultString) > 0

  14:          SET @ResultString = LEFT(@ResultString, LEN(@ResultString) - 1)

  15:   

  16:      RETURN @ResultString

  17:  END

And there you go! To use it you only have to do this:

   1:  SELECT dbo.fnCSVList(1, 50)

And you’ll get a list like this:

Berlin,Bordeaux,Bothell,Calgary,Cambridge,Dallas,Detroit,Duluth,Kenmore,Melbourne,Memphis,Montreal,Nevada,Orlando,Ottawa,Phoenix,Portland,San Francisco,Seattle 

I expect your comments on this solution. I’ve added the file with the two functions so you don’t have to type them.

See you….