pivot table

big mission today : explain the mess with pivot table.

let's look the following query
SQL> select extract(year from hiredate) year, count(*) from emp group by extract(year from hiredate) order by year
YEAR COU
----- ---
1980   1
1981  10
1982   1
1987   2


how do we get the years without hiredate with 0?

1) with a table of years
it is fast, and easy to undestand. But it require to create a table

create table year(year number);
insert into year values (1980);
insert into year values (1981);
insert into year values (1982);
insert into year values (1983);
insert into year values (1984);
insert into year values (1985);
insert into year values (1986);
insert into year values (1987);
insert into year values (1988);
insert into year values (1989);

SQL> select year, count(hiredate) COU from emp right join year on (extract(year from hiredate)=year) group by year order by year
YEAR COU
----- ---
1980   1
1981  10
1982   1
1983   0
1984   0
1985   0
1986   0
1987   2
1988   0
1989   0


2) I have a few artifice to select from dual
A. union
SQL> select year, count(hiredate) COU from emp right join
(select 1980 year from dual
union all select 1981 from dual
union all select 1982 from dual
union all select 1983 from dual
union all select 1984 from dual
union all select 1985 from dual
union all select 1986 from dual
union all select 1987 from dual
union all select 1988 from dual
union all select 1989 from dual)
on (extract(year from hiredate)=year) group by year order by year;


ok for 10 rows, but not for 1000!

B. cube
a trick, which may not work in all versions
SQL> select year, count(hiredate) COU from emp right join
(select 1980+rownum year from
(select null from dual group by cube (null,null,null,null))
where rownum<11)
on (extract(year from hiredate)=year) group by year order by year;


not ok if the number of rows to be generated is volatile, sometimes 1, sometimes 1000000

C. connect by
even more dirty tricky, no guarantee that it will work

SQL> select year, count(hiredate) COU from emp right join
(select 1980+level year from dual connect by level<11)
on (extract(year from hiredate)=year) group by year order by year
/


3) use rownum and all_objects
SQL> select year, count(hiredate) COU from emp right join
(select 1980+rownum year from all_objects where rownum<11)
on (extract(year from hiredate)=year) group by year order by year
/


not scalable, you do not know how many rows are in all_objects (500,5000,50000?), and not performant at all, because all_objects is a complex view

4) PL/SQL pipelined table
perfectly scalable, quite fast, requires to create a few objects

SQL> create type t_year as TABLE OF number;
/

Type created.

SQL> create or replace function f_year(n1 number, n2 number) return t_year pipelined is
begin for i in n1..n2 loop pipe row(i); end loop; return; end;
/

SQL> select COLUMN_VALUE year, count(hiredate)
from emp right join
table(f_year((select min(extract(year from hiredate)) from emp), (select max(extract(year from hiredate)) from emp)))
on ( COLUMN_VALUE = extract(year from hiredate) )
group by COLUMN_VALUE
YEAR COU
----- ---
1980   1
1981  10
1982   1
1983   0
1984   0
1985   0
1986   0
1987   2


I like the last one, because you can return 1 or 1000000000 rows, the query will not change. It will be fast for 1 row, and slow but will still work for 1000000000 rows.


pivot table part 2
pivot table part 3