title: Combine multiple rows in one from same table
body: <p>I am not quite sure if this is possible through SQL query alone. </p>
  
  <p>Let's say I have a table with the following data and structure:</p>
  
  <pre><code>ID  |  Item Code  |   Store Name  |  Store Price
  1   |     101     |      Red      |    10.00
  2   |     101     |      Blue     |     9.75
  3   |     102     |      Green    |    11.50
  4   |     103     |      Black    |     5.75
  5   |     103     |      Yellow   |     4.50
  6   |     103     |      Purple   |     6.00
  </code></pre>
  
  <p>And I want to have a result like this: </p>
  
  <pre><code>ItemCode | Store1Name | Store1Price | Store2Name | Store2Price | Store3Name | Store3Price
    101    |    Red     |   10.00     |    Blue    |    9.75     |            |
    102    |    Green   |   11.50     |            |             |            | 
    103    |    Purple  |    6.00     |    Black   |    5.75     |   Yellow   |    4.50
  </code></pre>
  
  <p>I am currently trying on using JOINS to solve this problem but still can't get the desired result.</p>
  
  <p>Sample JOIN I created:</p>
  
  <pre><code>SELECT A.ItemCode [ItemCode], A.StoreName [Store1Name], A.StorePrice [Store1Price],
         B.StoreName [Store2Name], B.StorePrice [Store2Price],
         C.StoreName [Store3Name], c.StorePrice [Store3Price]
  FROM   tblStorePrice A
           LEFT JOIN tblStorePrice B ON A.ItemCode = B.ItemCode AND A.ID &lt;&gt; B.ID
           LEFT JOIN tblStorePrice C ON A.ItemCode = C.ItemCode AND A.ID &lt;&gt; C.ID
  </code></pre>
  
  <p>Note:</p>
  
  <p>The table only stores three stores per Item Code (at maximum). Item Code with less than 3 stores should have null values for the result.</p>
  
  <p>Hoping to have positive feedback and response. Thanks in advance, guys! :)</p>