Can I combine a PIVOT with an Inner join in microsoft SQL server?

Question

I have the following SQL query:

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

This returns something that looks like this:

CountryID CountryGDPPerCapita LifeExpTotal

3 35985.78 77.24

5 9147.7 74.54

Then I have another query as follows:

SELECT CountryName, CountryGDP, CountryGDPGrowth 
FROM globaledge.dbo.Country_Statistics 
WHERE CountryID IN (3,5) 
AND year=2002
Order By CountryName

Which produces the following:

CountryName CountryGDP CountryGDPGrowth

Mexico 1567000000000000 1.3

United States 14440000000000000 0.4

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:

CountryName CountryGDP CountryGDPGrowth CountryGDPPerCapita LifeExpTotal

Mexico 156700000000000000 1.3 35985.78 77.24

United States 144400000000000000 0.4 9147.7 74.54

Could anyone help me make this query? or tell me if it's possible?

Solution

Something like this would work:

SELECT 
  a.CountryID, a.CountryName, a.CountryGDP, a.CountryGDPGrowth 
, b.CountryGDPPerCapita, b.LifeExpTotal
FROM
(
    SELECT CountryID, CountryName, CountryGDP, CountryGDPGrowth 
    FROM globaledge.dbo.Country_Statistics 
    WHERE CountryID IN (3,5) 
    AND year=2002
) AS a
JOIN 
(
    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
) AS b ON a.CountryID = b.CountryID
Order By a.CountryName