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
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;