Efficient string concatenation in SQL?
We have 2 tables one-to-many. How can we fetch parent table field, and the second field is its children comma separated ? In MSSQL 2000 we could use the following function. But in MSSQL 2005 with the help of FOR XML PATH feature it is a lot easier and the performance of string concatenation is amazing.SELECT CustomerID
,(SELECT CAST(OrderID AS VARCHAR(MAX)) + ',' AS [text()]
FROM dbo.Orders AS O
WHERE O.CustomerID = C.CustomerID
ORDER BY OrderID
FOR XML PATH('')) AS Orders
FROM dbo.Customers AS C;