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)

   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)