Concat groups in SQL Server [duplicate]

Solution 1:

For a clean and efficient solution you can create an user defined aggregate function, there is even an example that does just what you need.
You can then use it like any other aggregate function (with a standard query plan):

Solution 2:

This will do:

SELECT mt.ID,
       SUBSTRING((SELECT mt2.Value
                  FROM   MyTable AS mt2
                  WHERE  mt2.ID = mt.ID
                  ORDER BY mt2.VALUE
                  FOR XML PATH('')), 3, 2000) AS JoinedValue
FROM   MyTable AS mt

Solution 3:

See:

http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html