title: Can I combine a PIVOT with an Inner join in microsoft SQL server?
body: <p>I have the following SQL query:</p>
  
  <pre><code>SELECT CountryID, [10201] AS CountryGDPPerCapita, [10677] AS LifeExpTotal
  FROM
  (
      SELECT CountryID,FieldID,numeric 
      FROM globaledge.dbo.DIBS_Data
      WHERE CountryID IN (3,5)
      AND FieldID IN (10201,10677)
      AND year = 2002
  )  SourceTable
  PIVOT
  (
      MAX(numeric)
      FOR FieldID IN ([10201],[10677])
  ) AS PivotTable
  ORDER BY PivotTable.CountryID
  </code></pre>
  
  <p>This returns something that looks like this:</p>
  
  <p>CountryID       CountryGDPPerCapita       LifeExpTotal</p>
  
  <p>3                 35985.78                 77.24</p>
  
  <p>5                 9147.7                   74.54</p>
  
  <p>Then I have another query as follows:</p>
  
  <pre><code>SELECT CountryName, CountryGDP, CountryGDPGrowth 
  FROM globaledge.dbo.Country_Statistics 
  WHERE CountryID IN (3,5) 
  AND year=2002
  Order By CountryName
  </code></pre>
  
  <p>Which produces the following:</p>
  
  <p>CountryName      CountryGDP        CountryGDPGrowth</p>
  
  <p>Mexico           1567000000000000 1.3</p>
  
  <p>United States       14440000000000000 0.4</p>
  
  <p>Also note, I do have CountryID in both tables, that refer to the same country.  What I want is to create one SQL Query, maybe with an INNER JOIN, that would return the following:</p>
  
  <p>CountryName   CountryGDP CountryGDPGrowth CountryGDPPerCapita LifeExpTotal</p>
  
  <p>Mexico 156700000000000000 1.3 35985.78  77.24</p>
  
  <p>United States 144400000000000000 0.4  9147.7 74.54</p>
  
  <p>Could anyone help me make this query? or tell me if it's possible?</p>