Mysql Query: display multiple values in a single column

Question

I've got the following query:

  $query = db_select('commerce_order', 'cord')->extend('TableSort');
  $query->join('commerce_line_item', 'li', 'cord.order_id = li.order_id');
  $query->join('commerce_product', 'item', 'item.sku = li.line_item_label');
  $query->fields('cord', array('order_id'))
        ->fields('li', array('quantity'))
        ->fields('item', array('title', 'sku'))
        ->groupBy('title')

        ->orderByHeader($header); 
  $query->addExpression('SUM(quantity)', 'quantity');      
  $result = $query->execute();

the results are grouped by 'title' as you can see. one of the rows looks like this:

product1 - 0000003 - 3.00 - 612

3.00 is the quantity and 612 is one of the order id's. Order 612 contains a quantity of 2, and order 615 contains a quantity of 1. How can I show order id 615 next to order id 612? So the row looks like this:

product1 - 0000003 - 3.00 - 612, 615

I hope you get the idea, thanks in advance for your assistance

SOLUTION

$query->addExpression("GROUP_CONCAT(li.order_id separator ', ')", 'order_id'); 

Answer 1

In MySQL, you can add an expression GROUP_CONCAT(order_id), which will be a comma-separated list of all the values per group. See the manual on GROUP_CONCAT() for more details.

If you're not using MySQL, you'll have to use another solution. GROUP_CONCAT is a function specific to MySQL, but some other RDBMS brands have some equivalent way of doing the same thing.