Rows to field level in query?

Question

Hello,

Using SQL 2005, I am trying to run a query on a Orders table in my database. Each order can have multiple 'Comments'.

I want to get a single order record with all the Comments put together in one field.

Here is the psuedocode for what I'm trying to do:

SELECT 
Orders.Name, 
Orders.Date,
(SELECT Comment += Comment FROM OrderComments WHERE OrderComments.OrderId = Orders.OrderId)
FROM Orders

I want the Order Comments to end up as a field in my query, and be able to seperate the resulting rows by something like the date and a comma. Is there a way to merge the resulting rows from the subquery into one field?

So if there's:

**Orders:**
Order Id(int): 1
Order Name(varchar): First Order

**OrderComments:**
OrderId(int): 1
Comment(text): First order, yay

OrderId(int): 1
Comment(text): Just a random comment

I want to end up with a row like this(in this case a semi-colon delimiter): "First Order", "10/25/2010", "First order, yay; Just a random comment".

Thank you for any assistance!

Answer 1

Use for xml:

SELECT 
Orders.Name, 
Orders.Date,
(SELECT Comment +', ' FROM OrderComments WHERE OrderComments.OrderId = Orders.OrderId FOR XML PATH(''))
FROM Orders