pivot table part 3

one more try with model, available 10gR1

select * from 
  (select extract(year from hiredate) h, count(*) c 
    from emp 
    group by extract(year from hiredate))
model
  dimension by (h)
  measures (c)
  rules(
    c[FOR h FROM 1980 to 1990 INCREMENT 1] = 
    case when c[CV()] is present then c[CV()] else 0 end)
order by h;

         H          C
---------- ----------
      1980          1
      1981         10
      1982          1
      1983          0
      1984          0
      1985          0
      1986          0
      1987          2
      1988          0
      1989          0
      1990          0

pivot table part 1
pivot table part 2

Published by

Laurent Schneider

Oracle Certified Master

Leave a Reply