Tuesday, July 29, 2008

T-SQL 2005: One-to-many comma separated

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 CAST(OrderID AS VARCHAR(MAX)) + ',' AS [text()]
FROM dbo.Orders AS O
WHERE O.CustomerID = C.CustomerID
FOR XML PATH('')) AS Orders
FROM dbo.Customers AS C;

T-SQL 2005: One-to-many comma separated

No comments: