Comma Separated Lists

Creating Comma Separated lists from table values is a very common need, and can be done many different ways.  I am referring to selecting values from multiple records into a single, comma-separated value.  See these two examples, referencing the Production.Culture table within AdventureWorks:


  -- Look at the data values

  USE AdventureWorks

  GO

  SELECT [Name]

  FROM Production.Culture
  GO


     Name
    Arabic
    Chinese
    English
    French
    Hebrew
    Invariant Language (Invariant Country)
    Spanish
    Thai

  --Method #1
  SELECT SUBSTRING(
  (SELECT ',' + c.Name
  FROM Production.Culture c
  ORDER BY c.Name
  FOR XML PATH('')),2,200000) [Cultures]
  GO

    Cultures
    Arabic,Chinese,English,French,Hebrew,Invariant Language (Invariant Country),Spanish,Thai


  --Method #2
  DECLARE @List varchar(3000)
  SELECT @List = COALESCE(@List + ', ', '') + [name]
  FROM Production.Culture
  SELECT @List [Cultures] 

    Cultures
    Arabic, Chinese, English, French, Hebrew, Invariant Language (Invariant Country), Spanish, Thai

Many people consider XML as the best solution for performance, but there are also many other ways to approach this.  I will be back soon with some more.