SQL PIVOT - MS SQL Server 2008

Question

Alright, I'm sure this answer exists out there, but I honestly don't know enough to phrase the question properly to find the answer by searching (I've definitely tried).

I'm trying to join multiple tables, and add columns where data is different (instead of adding a row of identical data except for the one column that is different).

As an example, let's say I have the following 2 tables.

Table name: COUNTRY

| Country ID |  Name  | Location | Climate |
+------------+--------+----------+---------+
|          1 | Canada | North    | Cold    |
|          2 | USA    | South    | Warm    |
|          3 | Russia | North    | Cool    |
+------------+--------+----------+---------+

Table Name: POPULATION

| Country ID | Year | Population |
+------------+------+------------+
|          1 | 1990 |         10 |
|          1 | 1995 |         15 |
|          1 | 2000 |         18 |
|          2 | 1990 |         12 |
|          2 | 1995 |         22 |
|          2 | 2000 |         25 |
|          3 | 1990 |          3 |
|          3 | 1995 |          5 |
|          3 | 2000 |          7 |
+------------+------+------------+

When I do a join with these, I would get three rows of "Canada" with the population values for each of the three years.

What I want though, is this:

|  Name   | Climate | Pop 1990 | Pop 1995 | Pop 2000 |
+---------+---------+----------+----------+----------+
| Canada  | Cold    |       10 |       15 |       18 |
| USA     | Warm    |       12 |       22 |       25 |
| Russian | Cool    |        3 |        5 |        7 |
+---------+---------+----------+----------+----------+

Answer 1

should be something like this:

select name,climate,[1990] 'Pop 1990',[1995] 'Pop 1995',[2000] 'Pop 2000'
from (
select 
c.name
,c.climate
,p.[year]
,p.[Population]
from [POPULATION] p 
join COUNTRY c
on p.[Country ID] = c.[Country ID]) t
pivot
( sum([Population])
for [year] in ([1990],[1995],[2000])
)pvt