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

1 thought on “pivot table part 2

  1. Laurent Schneider

    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)
    /

Comments are closed.