How do I concatenate strings from a column into a single row?

This question is asked quite frequently. People want to take a column like this: 
 

Color  ------  red  orange  blue  green

 
And return a resultset like this: 
 

Colors  -------------------------  red,orange,blue,green

 
This isn't exactly relational, and can certainly be handled by the presentation layer. However, there are kludges in SQL Server that will allow you to derive a solution. We'll start with the simple case above: 
 

CREATE TABLE Colors  (      Color VARCHAR(32)  )  GO    SET NOCOUNT ON  INSERT Colors SELECT 'red'  INSERT Colors SELECT 'orange'  INSERT Colors SELECT 'blue'  INSERT Colors SELECT 'green'  GO    DECLARE @colors VARCHAR(1024)    SELECT      @colors = COALESCE(@colors + ',', '') + Color  FROM      Colors    SELECT Colors = @colors  GO    DROP TABLE Colors  GO

 
(Note that you can add an ORDER BY clause to the SELECT, but this will not necessarily be obeyed... the resulting list will be displayed in an arbitrary order. Please observe the wording there—I'm not saying your ORDER BY will be ignored, just that it's not guaranteed to be obeyed. In most cases it will come back the way you'd expect, but it is quite possible that there are certain scenarios in which it will be arbitrary or, otherwise, not what you expected.) 
 
Now, that's great if you're only concerned with getting one list back. But let's say we have a table of athlete names, events and results. For each athlete, we want a row containing the full name and a comma-separated list of events they medaled in. In this case, we'll use a user-defined function to handle the concatenation for us, and a subquery to limit the output to one row per name. 
 

CREATE TABLE dbo.Medals  (      FullName VARCHAR(32),      Result VARCHAR(12),      Event VARCHAR(32),      PRIMARY KEY (Fullname, Event)  )  GO    CREATE FUNCTION dbo.GetMedalResult  (      @fullName VARCHAR(32)  )  RETURNS VARCHAR(8000)  AS  BEGIN      DECLARE @r VARCHAR(8000)      SELECT @r = ISNULL(@r+',', '')          + Result + ' in ' + Event          FROM dbo.Medals          WHERE FullName = @fullName      RETURN @r  END  GO      SET NOCOUNT ON  INSERT Medals SELECT 'Marie-Helene Premont', 'Silver', 'Mountain Biking'  INSERT Medals SELECT 'Adam van Koeverden', 'Bronze', 'K-1 1,000M Kayak'  INSERT Medals SELECT 'Kyle Shewfelt', 'Gold', 'Gymnastics Floor Exercise'  INSERT Medals SELECT 'Alexandre Despatie', 'Silver', '3-Meter Springboard'  INSERT Medals SELECT 'Michael Phelps', 'Gold', '400M Medley'  INSERT Medals SELECT 'Michael Phelps', 'Gold', '200M Medley'  INSERT Medals SELECT 'Michael Phelps', 'Gold', '200M Butterfly'  INSERT Medals SELECT 'Michael Phelps', 'Gold', '100M Butterfly'  INSERT Medals SELECT 'Michael Phelps', 'Gold', '4x200M Freestyle Relay'  INSERT Medals SELECT 'Michael Phelps', 'Bronze', '200M Freestyle'  INSERT Medals SELECT 'Michael Phelps', 'Bronze', '4x100M Freestyle Relay'  GO    SELECT      Fullname,      dbo.getMedalResult(Fullname)  FROM  (      SELECT          Fullname      FROM          Medals      GROUP BY          Fullname  ) i  GO    DROP TABLE Medals  DROP FUNCTION dbo.getMedalResult  GO

 
Again, you will not be able to dictate the ordering of the result in each row; this will be decided by the optimizer at runtime, and will not necessarily stay consistent between executions. 
 
For some more thorough examples, please see this thread starring Anith Sen. 
 
SQL Server 2005 adds some interesting options for this kind of non-relational query. You should be able to have more granular control over concatenation with user-defined aggregates, and you can also take advantage of the new FOR XML PATH: 
 

USE AdventureWorks  GO    SELECT      CustomerID,      SalesOrderIDs = REPLACE(          (              SELECT                  SalesOrderID AS [data()]              FROM                  Sales.SalesOrderHeader soh              WHERE                  soh.CustomerID = c.CustomerID              ORDER BY                  SalesOrderID              FOR XML PATH ('')          ), ' ', ',')  FROM      Sales.Customer c  ORDER BY      CustomerID

 
Another way is to use the new CROSS APPLY operator: 
 

USE AdventureWorks  GO    SELECT      CustomerID,      SalesOrderIDs = LEFT(o.list, LEN(o.list)-1)  FROM      Sales.Customer c  CROSS APPLY  (          SELECT              CONVERT(VARCHAR(12), SalesOrderID) + ',' AS [text()]          FROM              Sales.SalesOrderHeader s          WHERE              s.CustomerID = c.CustomerID          ORDER BY              SalesOrderID          FOR XML PATH('')      ) o (list)  ORDER BY      CustomerID