title: SQL Query that sorts information into two new columns based on a third column
body: <p>I have a table that stores basic site information, another one that stores the site address, and a third table for the phone. My phone table contains phone numbers, and the type of number.</p>
  
  <p>What I want to do is get my site information, and the main contact number and fax number. So far, my statement looks like this:</p>
  
  <pre><code>SELECT site.isiteid,
      site.iclientid,
      csitecode, 
      csitename,
      binactive,
      caddress1,
      caddress2,
      ccity,
      cstateid,
      czip,
      icountryid,
      cattention,
      cemail,
      cnumber,
      cextension
  FROM dbo.site 
      INNER JOIN dbo.address
          ON dbo.site.isiteid = dbo.address.isiteid
              AND (site.isiteid = 2)
      LEFT JOIN dbo.phone
          ON dbo.site.isiteid = dbo.phone.isiteid
              AND (dbo.phone.iphtypeid = 1)
  </code></pre>
  
  <p>This gets me all of the information that I need except for the fax number (dbo.phone.iphtypeid=3). Is there a way to add another column to the result called [fax], and populate it when site.isiteid=phone.isiteid AND phone.iphtypeid=3? Thus, the last 4 columns returned would be <code>[cemail][cnumber][cextension][cfax].</code></p>
  
  <p><strong>RESOLVED</strong></p>
  
  <p>Thank you to all three who answered. All the answers were similar, so I selected the one that had the most detailed explanation. I did need to add the table name to both cnumber references and cextension to avoid ambiguity. Thank you for the responses!</p>