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 ' ' + 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.