Grouping records in one single row

There are times when you need to group records which are linked by a common field (a foreign key) together into one single row. For example if you wanted to group all orders associated with a customer into one single row, it causes a problem in mysql because you can easily achieve a similar but yet different resultset by listing all the records associated with the foreign key.

There’s something which you can use in MySql to group the results together, it’s called the group_concat function. For values which are short, this will work fine in its default mode. However if you’re trying to concatenate long text values, the result will get truncated because by default it allows 1024 bytes of data. You can set the maximum to be something like 65000 bytes which will give you roughtly 5 whole pages of MS Word documents’ worth.

comments powered by Disqus