asked on

Pivoting in SQL using the 10g

I have a table having few data and the shift details for one month.
For the whole month the shift are going to be same so now I want to know on what date is a EMP is in which shift.

My source table structure is :

CREATE TABLE SHIFT_SUMMARY_NEW
(
SRNO NUMBER,
YEAR NUMBER(4),
MONTHS VARCHAR2(3),
DBA_NAME VARCHAR2(50),
SUN VARCHAR2(50),
MON VARCHAR2(50),
TUE VARCHAR2(50),
WED VARCHAR2(50),
THU VARCHAR2(50),
FRI VARCHAR2(50),
SAT VARCHAR2(50)
);


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

 CREATE OR REPLACE TRIGGER "MGMT"."SHIFT_MASTER_NEW_BR_I" 
  before insert on "SHIFT_SUMMARY_NEW"               
  for each row  
begin   
  if :NEW."SRNO" is null then 
    select "SHIFT_SUMMARY_NEW_SEQ".nextval into :NEW."SRNO" from dual; 
  end if; 
end; 
/
ALTER TRIGGER "MGMT"."SHIFT_MASTER_NEW_BR_I" ENABLE;

Open in new window

Now i have two records in my source table like as below:

YEAR MONTHS EMP SUN MON TUE WED THU FRI SAT
2011 MAR PINKURAY WEEK OFF WEEK OFF IST PST EST EST IST

Attached the file for the source and expected result set.

DATAFILE.xls

pinkuray

ASKER

I have only one record in this.
Now I want to pull the records in such away that I will get to know the shift details of a emp.

The shift will be fixed for the whole month.

For example if a EMP of in IST shift on MONDAY then he will be coming in IST for all mondays on that month.

Naveen Kumar

try this. It works fine for me.

select to_char(to_date(months || '-' || year,'MON-YYYY') + (level-1) , 'DY') Day,
to_date(months || '-' || year,'MON-YYYY') + (level-1) work_date,
dba_name,
decode(to_char(to_date(months || '-' || year,'MON-YYYY') + (level-1) , 'DY'),
'SUN',SUN,
'MON',MON,
'TUE',TUE,
'WED',WED,
'THU',THU,
'FRI',FRI,
'SAT',SAT) shift_type
from dual , shift_summary_new
connect by level <= ( select to_number(to_char(last_day(to_date(months || '-' || year,'MON-YYYY')),'DD')) from shift_summary_new )

pinkuray

ASKER

its giving me errror when I run your query:

ORA-01427: single-row subquery returns more than one row

Naveen Kumar

how many records are there in your shift summary new table ?

Naveen Kumar

see the query output which i had given in the attached document.
ee.doc

Naveen Kumar

you should have only one record in the shift_summary_new table otherwise it will give that error.

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pinkuray

ASKER

YES true.

A quick question can we write some trigger on the mail table like ..
is any record is inserted into shift_summary_new table then it will insert all the 31 or monthly records into another table which is as similar to this result ?

pinkuray

ASKER

I need to insert more records in shift_summary_new table where it will help me to insert into the 2nd result table.

Naveen Kumar

please consider that as a new question and i will post my update shortly as i do not want to discuss regarding that in this question.

pinkuray

ASKER

SURE ...

my other question link is :

https://www.experts-exchange.com/questions/26863485/trigger-in-oracle.html

pinkuray

ASKER

Thanks a lot

Naveen Kumar

Good and thanks