pivot table part 2

One more try with 10gR2

select to_number(column_value) HIREDATE, count(decode(to_number(extract(year from hiredate)), to_number(column_value), 1)) COUNT
from
emp,xmltable(‘for $i in 1980 to 1990 return $i’ )
group by to_number(column_value)
order by to_number(column_value)
/
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 3

One thought on “pivot table part 2”

  1. much more logic is :

    select to_number(column_value), count(empno)
    from emp right outer join
    xmltable(‘for $i in 1980 to 1990 return $i’) on (extract(year from hiredate) = to_number(column_value))
    group by to_number(column_value)
    /

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>