sql - Pivot for the same gender -

how have multiple pivot. achieve result highlighted below.
each grade , each gender, have totala , total b values aligned in 4 columns in single row. final result need contain 10 columns shown below.

my desired output [need contain 2 rows gender column remained]: tried below: script removed gender column , unable pivot 2 columns (totala, totalb) 4 additional columns @ same time.

   select *,   [totala_male] = [m],   [totalb_female] = [f] (   select * table) s   pivot    (     max(totala) [gender] in ([m],[f])   ) p 

i don't think want pivot @ all. looking find partial sum of total column, grouped key columns (it looks country , grade in case) . window functions let perform partial sum. however, won't filter gender. you'll need use case expression inside sum() include male or female in partial sums:

select *,        sum(case when gender = 'm' totala else 0 end) over(partition country, grade) totala_male,        sum(case when gender = 'f' totala else 0 end) over(partition country, grade) totala_female,        sum(case when gender = 'm' totalb else 0 end) over(partition country, grade) totalb_male,        sum(case when gender = 'f' totalb else 0 end) over(partition country, grade) totalb_female   totals 

see also: https://msdn.microsoft.com/en-us/library/ms189461.aspx

basically, window functions let group by part of single column expression in select list. result of aggregate , group included in every row, if other expression. note how there no group by or pivot in rest of query. partition by in over() clause works group by, specifying how group rows in resultset purposes of performing specified aggregation (in case, sum()).