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.


Call

T: 281-607-1333   

Houston, TX

Columbus, OH

  • White LinkedIn Icon
  • White Facebook Icon
  • White Twitter Icon
  • White Instagram Icon

[service​s]

[products]

[stay in touch]

- Blaze IT LLC -

  • White LinkedIn Icon
  • White Facebook Icon
  • White Twitter Icon
  • White Instagram Icon

[erp software consulting]