 <p>Tobias Ternstrom at Microsoft has a workaround that works for sql server2005 and higher: Consider the following example using the AdventureWorks microsoft sample database:</p> <pre><code> SELECT soh.OrderDate ,( SELECT p.Name + ', ' FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID WHERE sod.SalesOrderID = soh.SalesOrderID FOR XML PATH(''), TYPE ).value('/', 'NVARCHAR(max)') AS ProductsOrdered FROM Sales.SalesOrderHeader AS soh </code></pre> <p>See where they discuss the same problem here: <a href="https://connect.microsoft.com/SQLServer/feedback/details/389883/string-aggregate-function-something-like-sum-for-numbers" rel="nofollow">string aggregate function, something like SUM for numbers</a></p>