Can I combine PIVOT with an internal connection on a Microsoft 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 

Returns what looks like this:

CountryID CountryGDPPerCapita LifeExpTotal

3 35985.78 77.24

5 9147.7 74.54

Then I have the following query:

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

Which gives the following:

CountryName CountryGDP CountryGDPGrowth

Mexico 1567000000000000 1.3

United States 1,444,000,000,000,000 0.4

Also note that I have a CountryID in both tables that apply to the same country. I want to create a single SQL query, possibly with an INNER JOIN, which will return the following:

CountryName CountryGDP CountryGDPGrowth CountryGDPPerCapita LifeExpTotal

Mexico 156700000000000000 1.3 35985.78 77.24

United States 1,444,000,000,000,000 0.4 9147.7 74.54

Can someone help me make this request? or tell me, is this possible?

Answer 1

Something like this will 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