Use PIVOT to Create a Cross-Tabulation

This query will generate a table that shows each employee and the number of days they have called in for either Sick, Bereavement or MISC.  The leave_type_code is a single database column, but the PIVOT allows you to make several columns from its values.

WITH pivot_data AS (
select ed.employee_id NAME, psa.leave_type_code LTC, psa.shift_date SHIFT_DATE
from
employee_detail ed, pa_shift_allocation psa
where ed.employee_id=psa.employee_id
and ed.is_fake='N'
and psa.assignment_type_code='L'
and psa.shift_date BETWEEN '01-JAN-10' and '31-DEC-10')
select * from pivot_data
pivot (count(shift_date) for LTC in ('S' as SK,'B' as BV,'M'as MISC))
order by SK DESC;

NAME                 SK                     BV                     MISC                  
-------------------- ---------------------- ---------------------- ----------------------
EM1018               121                    0                      0                     
EM1034               61                     5                      0                     
EM1022               60                     0                      156                   
EM1019               60                     0                      154                   
EM1065               35                     1                      0                     
EM1107               33                     5                      0                     
EM1029               24                     0                      0                     
EM1136               23                     2                      0                     
EM1134               21                     0                      0                     
EM1151               21                     2                      0                     
EM1025               19                     0                      40