Suppose you had a requirement in which you needed to return one row which had two columns. The two columns would contain the number of employees in the two different departments. This sounds like a trivial problem to solve but isn’t as easy when you get to coding it.
One way to do this is to use
case statements in the aggregation function such as :
Another neat way to do this is to use the
select e.deptno, d.dname
from emp e, dept d
and e.deptno = d.deptno)
pivot (count(deptno) as dept_cnt for (dname) in ('ACCOUNTING' as acct, 'RESEARCH' as rsch))
In this example they both have the same explain plan as shown below. If using on larger / more complex data sets it would be a good idea to compare the explain plans for both queries to see if there’s performance gain between the two.