oracle11g - Beginner Oracle 11g SQL - Create Pivot Table from joining multiple tables -

i have multiple tables, each fk relationships connect them 1 another. need create pivot table using details out of of tables.

region table region_id|region_description  state table state_id|state_description|region_id_fk  order table order_id|order_date|state_id_fk  category table category_id|category|description|order_id_fk 

i joining tables using natural join, based on fks.

i need determine how many orders in each category each region.

the resulting table should this:

 category|region1|region2|region3|total  sporting    1       0      3      4  etc         0       2      1      3 

select c.category,        count( case r.region_id when 1 1 else null end ) region1,        count( case r.region_id when 2 1 else null end ) region2,        count( case r.region_id when 3 1 else null end ) region3,        count( case r.region_id when 4 1 else null end ) region4    region r        inner join        state s        on (r.region_id = s.region_id_fk)        inner join        order o        on (s.state_id = o.state_id_fk)        inner join        category c        on (o.order_id = c.order_id_fk) group c.category