Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Pivoting in SQL using the 10g

Posted on 2011-03-03
13
Medium Priority
?
514 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:pinkuray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 4

Author Comment

by:pinkuray
ID: 35034219
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.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35034250
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 )
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35034277
its giving me errror when I run your query:

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

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35034285
how many records are there in your shift summary new table ?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35034296
see the query output which i had given in the attached document.
ee.doc
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35034298
you should have only one record in the shift_summary_new table otherwise it will give that error.
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 2000 total points
ID: 35034308
if you have more than one record in the table, but you want to get the output for one particular DBA, then use this query.

I have tested this and it works fine for me. You need to change PINKURAY to some other DBA name if you want to run the query for that DBA.

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 , ( select * from shift_summary_new where dba_name ='PINKURAY')
connect by level <= ( select to_number(to_char(last_day(to_date(months || '-' || year,'MON-YYYY')),'DD')) from shift_summary_new
where dba_name ='PINKURAY' )
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35034310
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 ?
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35034314
I need to insert more records in shift_summary_new  table where it will help me to insert into the 2nd result table.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35034317
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.

0
 
LVL 4

Author Comment

by:pinkuray
ID: 35034349
0
 
LVL 4

Author Closing Comment

by:pinkuray
ID: 35034351
Thanks a lot
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35034428
Good and thanks
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question