SQL Tip - Easily merge a field in multiple rows

There are cases when you might need to display data stored in multiple rows in a single field.

For example, let's say you have a table with sales order information and a separate table that stores comments that the user has entered about that order. But the comments are stored in separate rows with a sequence number associated with each row.

This would be fine if you are adding the comments using a sub-report or something similar. But let's say that you want to display the comments in a single field in a report. You could create a custom function to concatenate the data, but there's an even easier method!

SELECT

order_number,

(SELECT ' ' + rtrim(COM.comment)

FROM order_comments COM

WHERE COM.order_number = OC.order_number

FOR XML PATH('')) [Comment]

FROM order_comments OC

GROUP BY OC.order_number

ORDER BY 1

Now you can easily join this data to your order table and use it in reports.