Dear All,

I want to generate a table from a source table like

staff_id
record_time
record_type: i.e. 1=Day In;2=Lunch start; 3=Lunch End;4=Day Out

Therefore, basically, if the staff comes for whole day, he/she could have 4 records in the source table e.g.

staff_id   Record_time   Record_type
--------------------------------------
1   2006-09-01 09:00:00   1
1   2006-09-01 13:00:00   2
1   2006-09-01 14:30:00   3 
1   2006-09-01 18:00:00   4 

To generate a view for "timecard", I want to have a cursor table like this:

staff_id   record_date  day_in   lunch start    Lunch end   Day_out
----------------------------------------------------------------------
1     2006-09-01     09:00:00   13:00:00     14:30:00    18:00:00

PLEASE HELP