Crosstab challenge !!

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:

Code:

---

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?

Code:

---

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

---

Any advice greatly appreciated!

Thanks

Ben

Try this:

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

Highlander,

Cheers for the steer but no joy i'm afraid. ORA-00937: not a single-group group function.

Your idea sent me on convuloted round trip that eneded up with the following solution though.

Code:

---

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

---

Thanks

Ben