title: SQL combine columns based on another column
body: <p>I've got a list of items and two locations (X and Y) that these items can be in.these two locations have these items in different quantities.
  So When someone places an order for a few items, the items can be pulled from either of these two locations. </p>
  
  <p>Below is the 'Orders' table I've created but it shows two columns for two locations and available stock. </p>
  
  <pre><code>ItemNumber          Location    Stock X     Stock Y
  A                      X          12          32
  B                      X          10          54
  C                      X           5          23
  A                      Y          54          30
  C                      Y          65          36
  D                      Y          76          23
  E                      X          12          31
  F                      X          32          19
  F                      Y          72          40
  </code></pre>
  
  <p>What I want to see is available stock for the requested location in a column, not both locations and stock availability in two columns as I've done above.
  Result table I Want to see is,</p>
  
  <pre><code>ItemNumber          Location    Avail Stock
  A                      X          12          
  B                      X          10          
  C                      X           5          
  A                      Y          30
  C                      Y          36
  D                      Y          23
  E                      X          12          
  F                      X          32          
  F                      Y          40
  </code></pre>
  
  <p>I just cant get my head around this to do it. great if anyone could help or tell me if its even possible.</p>
  
  <p>Thanks</p>