Thursday, March 24, 2011

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
  SELECT [Name]
  FROM Production.Culture

    Invariant Language (Invariant Country)

  --Method #1
  (SELECT ',' + c.Name
  FROM Production.Culture c
  ORDER BY c.Name
  FOR XML PATH('')),2,200000) [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] 

    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.

No comments:

Post a Comment