Populating data using a Pivot table

Question

I have a table like this,

country 2007    2008    2009
 UK      5       10      20
 uk      5       10      20
 us     10       30      40
 us     10       30      40

But I want to populate the table like this,

country year    Total volumn
 uk     2007    10
 uk     2008    20
 uk     2009    40
 us     2007    20
 us     2008    60
 us     2009    80

How do I do this in SQL server 2008 using Pivot table or any other method.

http://sqlfiddle.com/#!3/2499a

Answer 1

SELECT country, [year], SUM([Total volumn]) AS [Total volumn]
FROM (
      SELECT country, [2007], [2008], [2009]
      FROM dbo.test137
      ) p
UNPIVOT 
([Total volumn] FOR [year] IN ([2007], [2008], [2009])
) AS unpvt
GROUP BY country, [year]
ORDER BY country, [year]

See demo on SQLFiddle