Generating a comma separated list in SQL Server without using cursors

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

 

6 Comments

  • Nice hint ... I like this part in the select statement:

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

    Actually, this is the magical part!

  • Nope!!!
    magical part is select DISTINCT.
    Am I right ? And please tell me why.

    The matter is when you remove distinct it will assign only the last one occurance.

  • SELECT
    CASE
    WHEN LEN(CommaSeparatedList) > 0 THEN LEFT(CommaSeparatedList, LEN(CommaSeparatedList) -1)
    ELSE ''
    END AS List
    FROM (
    Select
    (
    SELECT Isnull(prefix, '') + ' ' + IsNull(number, '') + ', '
    FROM dbo.CORE_telephone AS P
    ORDER BY prefix + ' ' + number
    FOR XML PATH('')
    ) as CommaSeparatedList
    ) as tbl

  • DECLARE @ResultString varchar(2048)

    select @ResultString = coalesce(@ResultString + ',','') + City from Person.Address where AddressID between 400 and 410
    select @ResultString

  • Suryakant Ker: that was a great simplification that met my needs exactly! I used:


    DECLARE @Cuisines VarChar(250)

    SELECT @Cuisines = coalesce(@Cuisines + ', ','') + CuisineDescr from ztCuisine CUI inner join ztLocationCuisine LCUI on LCUI.CuisineID = CUI.CuisineID where LCUI.LocationID = @LocationID order by LCUI.SortOrder

    SELECT @Cuisines

    ... worked great!

  • i have a result set like this:
    name: NPS
    2010/feb 6.5
    2010/march 7.5

    i need to add the names in comma separated .
    2010/feb/vinay,raju 6.5
    2010/march/ragu,raju 7.5

    can any one help this?

Comments have been disabled for this content.