Concat groups in SQL Server [duplicate]
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):
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 JoinedValueFROM MyTable AS mt