Search code examples
sqlsql-server-2008excelssas

Pivot table and get aggregation on multiple arguments

this is my base table:

Region year carsSold bicyclesSold
RegX   1999       50          100
RegX   2000      150           30
RegY   1998       60           40

What I would like to have as a result is:

Region Year          1998 1999 2000
RegX   carsSold         0   50  150
       bicyclesSold     0  100   30  
RegY   carsSold        60    0    0
       bicyclesSold    40    0    0

Is there any way to do it in sql server ? Is there a way to do this in excel ? What is the best solution for such data presentation. Could You suggest any software appropriate for that ?

I know about Pivoting tables in sql server but cant write anything what would give result close to what excactly I want

Solution 
  • select P.Region,
       P.Item,
       isnull(P.[1998], 0) as [1998],
       isnull(P.[1999], 0) as [1999],
       isnull(P.[2000], 0) as [2000]
from YourTable
unpivot (ItemsSold for Item in (carsSold, bicyclesSold)) as U
pivot (sum(ItemsSold) for year in ([1998], [1999], [2000])) as P
order by P.Region, P.Item

    SQL Fiddle