One-to-many comma separated with ‘FOR XML PATH’ in TSQL/2005

original post: One-to-many comma separated

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;
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s