title: Combine multiple rows into one in MySQL without group concat
body: <p>I have a table in MySQL in which I need to join multiple rows. The table look like this:</p>
  
  <pre><code>Order_ID  |  Item_ID  |  color
  1           1           white
  1           2           red
  2           1           black
  2           1           blue
  </code></pre>
  
  <p>I want to combine the rows according to their Order_ID which will yield a result that looks like this:</p>
  
  <pre><code>Order_ID  |  Item_1_ID  | Item_1_color  | Item_2_ID  | Item_2_color
  1           1             white           2            red
  2           1             black           1            blue
  </code></pre>
  
  <p>I know that there's a way to combine two rows into one by using group concat, but I want to do it so that each field remains separated, not joined as if using group concat.</p>
  
  <p>I already tried using statement join too, something like this:</p>
  
  <pre><code>SELECT o.*, o1.* FROM order o INNER JOIN order o1 ON o.Order_ID = o1.Order_ID
  </code></pre>
  
  <p>But for a few cases the rows that I want to join is too much, exceeding the limit of 61 join in MySQL.</p>
  
  <p>Any help would be appreciated, thanks.  </p>
  
  <p>---edit---</p>
  
  <p>There was this other parameter called group which determined the number of items per order. The table looks like this:</p>
  
  <pre><code>Order_ID   |  group
   1           1
   2           1
  </code></pre>
  
  <p>So if the order belongs to the same group they will have the same number of columns, but between one group and another the number of columns may be different. And my query is per one group only, so there's no problem in the differences between number of columns.</p>