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');

Bill Karwin · Accepted Answer · 2012-10-29 15:33:05Z

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.

answered Oct 29, 2012 at 15:33

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql Query: display multiple values in a single column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related