select
	 month(date) as month_order,
		sum(case year(date) when 2006 then 1 else 0 end) as year_2006,
		sum(case year(date) when 2007 then 1 else 0 end) as year_2007
from order
group by month(date)
order by month(date)

select   country,
	 month(date) as month_order,
		sum(case year(date) when 2006 then 1 else 0 end) as year_2006,
		sum(case year(date) when 2007 then 1 else 0 end) as year_2007
from order
group by country, month(date)
order by country, month(date)

select          year(date) as year_order,
		month(date) as month_order,
		sum(case when country = 'GB' then 1 else 0 end) as GB,
		sum(case when country = 'USA' then 1 else 0 end) as USA,
		sum(case when country = 'JAR' then 1 else 0 end) as JAR
from order
group by year(date), month(date)
order by year(date), month(date)

 declare @t table (mydate datetime, country varchar(10),product varchar(10))

insert into @t
			select '20070101','GB','prod1'
union all	select '20070102','GB','prod2'
union all	select '20070103','GB','prod1'
union all	select '20070101','USA','prod1'
union all	select '20070102','USA','prod2'
union all	select '20070103','USA','prod2'
union all	select '20070104','USA','prod2'


select         year(mydate) as year_order,
		month(mydate) as month_order,
		sum(case when country = 'GB' then 1 else 0 end) as GB,
		sum(case when country = 'USA' then 1 else 0 end) as USA,
		sum(case when product = 'prod1' then 1 else 0 end) as product1,
		sum(case when product = 'prod2' then 1 else 0 end) as product2,
		sum(case when product = 'prod1' and country = 'GB' then 1 else 0 end) as GB_product1,
		sum(case when product = 'prod2' and country = 'GB' then 1 else 0 end) as GB_product2,
		sum(case when product = 'prod1' and country = 'USA' then 1 else 0 end) as USA_product1,
		sum(case when product = 'prod2' and country = 'USA' then 1 else 0 end) as USA_product2
from @t
group by year(mydate), month(mydate)
order by year(mydate), month(mydate)