SQL Ouery in Oracle

Hello,

I have a table with few data, I want to have a report from that table as like below:

WORK_DATE      DBA      WORK_STATUS      SHIFT_TYPE
Tuesday, March 01, 2011      JOHN      Yes      Morning
Tuesday, March 01, 2011      PINKURAY      Weekoff      Null
Tuesday, March 01, 2011      MARK      Yes      Morning
Wednesday, March 02, 2011      JOHN      Yes      Morning
Wednesday, March 02, 2011      PINKURAY      Yes      Afternoon
Wednesday, March 02, 2011      MARK      Yes      Afternoon
Thursday, March 03, 2011      JOHN      Yes      Morning
Thursday, March 03, 2011      PINKURAY      Yes      Afternoon
Thursday, March 03, 2011      MARK      Yes      Afternoon
Friday, March 04, 2011      JOHN      Yes      Morning
Friday, March 04, 2011      PINKURAY      Yes      Afternoon
Friday, March 04, 2011      MARK      Yes      Afternoon
Saturday, March 05, 2011      JOHN      Weekoff      Null
Saturday, March 05, 2011      PINKURAY      Yes      Afternoon
Saturday, March 05, 2011      MARK      Weekoff      Null
Sunday, March 06, 2011      JOHN      Weekoff      Null
Sunday, March 06, 2011      PINKURAY      Yes      Afternoon
Sunday, March 06, 2011      MARK      Weekoff      Null
Monday, March 07, 2011      JOHN      Yes      Morning
Monday, March 07, 2011      PINKURAY      Weekoff      Null
Monday, March 07, 2011      MARK      Yes      Morning


The Source table having the data is been attached here and the structure of the table is like below:

 

/* 
W_DAY : "W" STANDS FOR THE WORKING , WHERE EVER THE W_ COLUMN WILL BE FILLED AS YES OR WEEKOFF
S_DAY : "S" STANDS FOR SHIFT, IT CAN BE Morning OR Afternoon OR Night OR General
*/

CREATE TABLE  "SHIFT_SUMMARY" 
   ( "SRNO" NUMBER, 
 "DBA" VARCHAR2(10), 
 "W_MON" VARCHAR2(50), 
 "S_MON" VARCHAR2(50), 
 "W_TUE" VARCHAR2(50), 
 "S_TUE" VARCHAR2(50), 
 "W_WED" VARCHAR2(50), 
 "S_WED" VARCHAR2(50), 
 "W_THU" VARCHAR2(50), 
 "S_THU" VARCHAR2(50), 
 "W_FRI" VARCHAR2(50), 
 "S_FRI" VARCHAR2(50), 
 "W_SAT" VARCHAR2(50), 
 "S_SAT" VARCHAR2(50), 
 "W_SUN" VARCHAR2(50), 
 "S_SUN" VARCHAR2(50), 
 "MONTH" VARCHAR2(50), 
 "YEAR" NUMBER, 
  CONSTRAINT "SHIFT_SUMMARY_PK" PRIMARY KEY ("SRNO") ENABLE
   )
/

CREATE SEQUENCE SHIFT_SUMMARY_SEQ MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20;

CREATE OR REPLACE TRIGGER  "SHIFT_SUMMARY_BR_I" 
  before insert on "SHIFT_SUMMARY"               
  for each row  
begin   
  if :NEW."SRNO" is null then 
    select "SHIFT_SUMMARY_SEQ".nextval into :NEW."SRNO" from dual; 
  end if; 
end; 

/
ALTER TRIGGER  "SHIFT_SUMMARY_BR_I" ENABLE
/

Select allOpen in new window

The expected result is  also attached here.

SAMPLE-DATA.xls