How can I make multiple records to be printed as a single row

Question

This three columns are taken from 3 tables. In other words, these records are retrieved by joining 3 tables.

It is basically a very simple time sheet that keeps track of shift starts time, lunch time and so on.

I want these four records to show in one row, for example:

setDate --- ShiftStarted --- LunchStarted --- LunchEnded ---- ShiftEnded ----- TimeEntered

Note: discard TimeEntered column. I will deal with this later, once i know how to solve the above issue, it will be easy for me to handle the rest.

How can i do it?

---

Further Info - Here is my query:

SELECT TimeSheet.setDate, TimeSheetType.tsTypeTitle
FROM TimeSheet 
INNER JOIN TimeSheetDetail ON TimeSheet.timeSheetID = TimeSheetDetail.timeSheetID
INNER JOIN TimeSheetType ON TimeSheetType.timeSheetTypeID = TimeSheetDetail.timeSheetTypeID

TimeSheet table consists of the following columns:

timeSheetID
employeeID - FK
setDate

setDate represents today's date.

TimeSheetType table consists of the following columns:

timeSheetTypeID
tsTypeTitle

tsTypeTitle represents shifts e.g. shift starts at, lunch starts at, shift ends at, etc.

TimeSheetDetail table consists of the following columns:

timeSheetDetailID
timeSheetID - FK
timeSheetTypeID - FK
timeEntered
addedOn

timeEnetered represents the time that employee set manually. addedOn represents the system time, the time that a record was inserted.

Answer 1

I must admit I haven't fully read all but I think you can work out the rest for yourself. Basically you can join the table timesheet with itself.

I did this ...

create table timesheet (timesheet number, setdate timestamp, timesheettype varchar2(200), timeentered timestamp);    
insert into timesheet values (1,to_date('2010-08-02','YYYY-MM-DD'),'Shift Started',current_timestamp);
insert into timesheet values (1,to_date('2010-08-02','YYYY-MM-DD'),'Lunch Started',current_timestamp);
insert into timesheet values (1,to_date('2010-08-02','YYYY-MM-DD'),'Lunch Ended',current_timestamp);
insert into timesheet values (1,to_date('2010-08-02','YYYY-MM-DD'),'Shift Ended',current_timestamp);
commit;

select * from timesheet t1  
left join  timesheet t2 on (t1.timesheet = t2.timesheet)
where t1.timesheettype = 'Shift Started'
  and t2.timesheettype = 'Lunch Started'

... and got out this

TIMESHEET   SETDATE                     TIMESHEETTYPE   TIMEENTERED                 TIMESHEET_1 SETDATE_1                     TIMESHEETTYPE_1   TIMEENTERED_1
1           02.08.2010 00:00:00.000000  Shift Started   05.08.2010  12:35:56.264075 1             02.08.2010 00:00:00.000000    Lunch Started     05.08.2010 12:35:56.287357

It was not SQL Server but in principle it should work for you too.

Let me know if you still have a question

Answer 2

You might want to check out the PIVOT operator. It basically allows you to use particular row values to create new columns in your result set.

You'll have to supply an aggregate function for combining multiple rows - for instance (assuming you split your data on a per day basis), you'll have to decide how to deal with multiple "shift started" events on the same day. Assuming that such events never occur, you'll still have to use an aggregate. MAX() is usually a safe choice in those circumstances.