title: SQL join on columns of same table with MAX on another column
body: <p>Have this table (top row is column names):</p>
  
  <pre><code>security    quote_type      price     round   
  sec_1       bid             3.4       1
  sec_1       ask             3.6       1
  sec_1       bid             3.5       2
  sec_1       ask             3.7       2
  sec_2       bid             5.2       1
  sec_2       ask             5.4       1
  sec_3       bid             2.3       1 
  sec_4       ask             7.8       1
  sec_4       ask             7.9       2
  </code></pre>
  
  <p>Need to achieve these results:</p>
  
  <pre><code>security    bid     ask      round
  sec_1       3.5     3.7      2 
  sec_2       5.2     5.4      1
  sec_3       2.3     null     1
  sec_4       null    7.9      2
  </code></pre>
  
  <p>Have this much working, which does not yet take into account the "round" column:</p>
  
  <pre><code>select security,
     max(case when quote_type = 'bid' then price end) as bid,
     max(case when quote_type = 'ask' then price end) as ask
  from t
  group by security;
  </code></pre>
  
  <p>There will always be at most one bid and one ask price per security, per round.</p>
  
  <p>Using SQL Server.</p>
  
  <p>Thanks for any help!</p>