Hello,
I have a table that I need to pivot. I am using oracle 11g R2 on my system and the server machine has oracle 10g running on it.
My table contains data:
Origin Table: ppl
ename | edate
emp1 | 01-10-17
emp1 | 01-10-17
emp1 | 01-10-17
emp1 | 02-10-17
emp2 | 01-10-17
emp3 | 01-10-17
emp3 | 01-10-17
emp3 | 02-10-17
emp3 | 02-10-17
Normally in pivot, output should be:
output table 1:
ename | 01-10-17 | 02-10-17
emp1 | 3 | 1
emp2 | 1 | 0
emp3 | 2 | 2
But here there is a condition that for each day count has to be >1. So desired output will be:
output table 2:
ename | 01-10-17 | 02-10-17
emp3 | 2 | 2
I used pivot in 11g to get required output and the output is partially correct.
select * from
(
SELECT ename, edate
FROM ppl
)
PIVOT
(
COUNT(edate)
FOR edate in('01-oct-2017', '02-oct-2017')
)
order by ename;
The above query returns only the pivot in output table 1.
My question 1 is where can I put filter in pivot query?
On trying to get the same output in 10g, I found out that pivot command is not supported there.
So here is my query for 10g:
select ename,
count(case when edate ='01-10-2017' then ename end) "01-OCT-2017",
count(case when edate ='02-10-2017' then ename end) "02-OCT-2017"
from ppl
group by ename
having count(case when edate ='01-10-2017' then ename end) >1
and count(case when edate ='02-10-2017' then ename end) >1
order by ename;
This gives the exact output 2 table as desired.
My question 2 is that the dates can range to 30 days even. As I can understand, for that case I need to write orange highlighted lines 30 times.
Will not such a big query will hamper the time of output? Or is there any feasible way to do this better?
Thank you for help..!!