CODEWITHSUNDEEP
sqlsql-servert-sql
user53885
user53885

Reputation: 3829

Rows to field level in query?

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!

Upvotes: 2

Views: 59

Answers (1)

Mark PM
Mark PM

Reputation: 2919

Use for xml:

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

Upvotes: 1

Related Questions