How to pivot dynamic array using PHP or SQL directly?

Question

I have the following PHP array. It store the information of a certain item, the store id and the price for that store. This array will be dynamic, so later more store_ids will be added.

array (size=6)
0 => 
    array (size=3)
      'item_id' => '1'
      'store_id' => '1'
      'price' => '74.99'
1 => 
    array (size=3)
      'item_id' => '2' 
      'store_id' => '1' 
      'price' => '35.99' 
2 => 
    array (size=3)
      'item_id' => '3' 
      'store_id' => '1' 
      'price' => '89.99' 
3 => 
    array (size=3)
      'item_id' => '1' 
      'store_id' => '2' 
      'price' => '69.99'
4 => 
    array (size=3)
      'item_id' => '2'
      'store_id' => '2'
      'price' => '39.99'
5 => 
    array (size=3)
      'item_id' => '3'
      'store_id' => '2'
      'price' => '95.99'

This is the result I want to get, I want to group them by the item_id and store every price each item has at every store. All the items will be available in all the store_ids.

array (size=3)
0 => 
    array (size=3)
      'item_id' => '1'
      (price for store_id 1) => '74.99'
      (price for store_id 2) => '69.99'
1 => 
    array (size=3)
      'item_id' => '2' 
      (price for store_id 1) => '35.99'
      (price for store_id 2) => '39.99'
2 => 
    array (size=3)
      'item_id' => '3' 
      (price for store_id 1) => '89.99'
      (price for store_id 2) => '95.99'

I was trying to do this directly from the query but I am using mysql and didn't find a solution for that. I know I need to loop through the array but I have no clue about the process needed to achieve this. Any help or suggestion is appreciated. Thanks!

something as `select id, group_concat(price) from table group by id` — splash58, Nov 14 '17 at 18:46
@splash58 But I want the values added as additional columns, not just concatenated with commas. — raptorandy, Nov 14 '17 at 18:54
@splash58 please close with https://stackoverflow.com/questions/39277227/how-to-create-a-pivot-array-in-php (I ran out of close votes today) — mickmackusa, Apr 09 '22 at 14:13

score 1 · Accepted Answer · answered Nov 14 '17 at 19:15

Not the best nor the most optimized code, but gives the expected result:

//$arr = ...
$output = array();
foreach($arr as $item){
    if(in_array($item['item_id'], array_column($output, 'item_id'))){
        // add store to already existing item
        $key = array_search($item['item_id'], array_column($arr, 'item_id'));
        $output[$key]['store_id_' . $item['store_id']] = $item['price'];
    }else{
        // add new item with store
        $tmp = array(
            'item_id' => $item['item_id'],
            'store_id_' . $item['store_id'] => $item['price'],
        );
        $output[] = $tmp;
    }
}

You can check it out.

How to pivot dynamic array using PHP or SQL directly?

1 Answers1