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;
Sunday, November 13, 2005 11:44 PM