Crosstab challenge !!

[BENDY]

Hi,

Does anyone know if crosstab queries are possible in SQL. As a simple example i've got 2 tables containing Sites and Tasks.

I'm trying to show the number of completed tasks per Region (as rows), per Phase (as columns). I can get close using the following decode statement:

select mysite.region, 
  decode(mysite.phase, 'Ph1', count(mytask.completed), 0) "Ph1",
  decode(mysite.phase, 'Ph2', count(mytask.completed), 0) "Ph2"
from mysite, mytask
where mytask.site = mysite.site
group by mysite.region, mysite.phase;

REGION	Ph1	Ph2
------- ------- --------
East	1	0
West	1	0
North	2	0
North	0	1
South	2	0
South	0	1

The trouble is get 1 row, per region, per phase.
Is it possible to combine these into 1 row per region as below?

REGION	Ph1	Ph2
------- ------- ----
East	1	0
West	1	0
North	2	1
South	2	1

Any advice greatly appreciated!

Thanks

Ben