3

How do I get a commma delimited result from the following example with no parameters and from a single query:

select FirstName + ' ' LastName from table_of_Names where NameType='game show host'

Bob Barker, Willie Aammes, Steve Allen, Clive Anderson

Thanks.

RetroCoder
  • 2,597
  • 10
  • 52
  • 81
  • possible duplicate of [Merge row values into a CSV (a.k.a GROUP_CONCAT for SQL Server)](http://stackoverflow.com/questions/7755815/merge-row-values-into-a-csv-a-k-a-group-concat-for-sql-server) – gbn Dec 13 '11 at 14:55
  • Or http://stackoverflow.com/q/6603319/27535 – gbn Dec 13 '11 at 14:57

2 Answers2

7

With parameters (better performance):

DECLARE @list VARCHAR(max)

SELECT @list = COALESCE(@list + ', ' , '') + FirstName + ' ' + LastName 
FROM table_of_Names where NameType='game show host'

SELECT @list

Without parameters (not as performant):

select stuff((select ','+ FirstName + ' ' + LastName
              FROM table_of_Names where NameType='game show host'
              for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '') as NameList
Chris Townsend
  • 3,042
  • 27
  • 31
  • Thanks that looks good, but I'm really trying to do this in a single query with no declare variables or parameters. – RetroCoder Dec 13 '11 at 15:06
  • @RetroCoder: in which case, look at the duplicates. This is a very common question... unless you want us to write the code for you... – gbn Dec 13 '11 at 15:09
  • lol, just wrote it for you... revised my answer based on your comment RetroCoder. – Chris Townsend Dec 13 '11 at 15:13
  • 2
    When you do this look at the actual execution plan. The first option with parameters is SO much faster than the STUFF example I included. – Chris Townsend Dec 13 '11 at 15:21
-3

The easiest way I know of is to write a stored procedure that iterates through the result set and appends the comma and space, for all rows except the last.

Gigi
  • 28,163
  • 29
  • 106
  • 188