Pivot Result Set
Date: Mon, 19 Oct 2009 12:11:16 -0700 (PDT)
Message-ID: <43966d12-df98-452b-9ae5-0e7326fbb2f3_at_o36g2000vbl.googlegroups.com>
Hi Guys
I have Report which display in Below format
Project Name:XYZ
Descrption ChargeNumber Start
End Mon Tue Wed Thu Fri
Sat Sun Total
3141 01/05/2009
01/11/2009 5 2 2
3 3 0 0 15
3141 01/12/2009
01/18/2009 8 8 8
8 8 0 0 40
Explanation : Lets Say i want to display timecard for all users from
date 01/Jan/2009 To 15/JAN/2009
Based on this i got two timecard as per above for date 01/05/2009 TO
01/11/2009 and 01/12 To 01/18
now i want to get data in following format where 1----16 coloumn are fixed
ChargeNumber Strt End 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 3141 01/05 01/18 0 0 0 0 5 2 2 3 3 0 0 8 8 8 8 0
i want 1--4 coloumn hours as 0 bcoz timecard start date is from 01/05 and coloumn 16 is zero bcoz report date is till 01/15
how can i achive this plz help me as i new at oracle
Anyhelp will be appreciate.
Here table information You need
CREATE TABLE TIMECARD.TC_CN_LN
(
TCCNL_SNBR NUMBER(8) NOT NULL, D_STRT_DT DATE, D_END_DT DATE, TOT_HRS NUMBER(6,2), MHRS NUMBER(6,2), THRS NUMBER(6,2), WHRS NUMBER(6,2), HHRS NUMBER(6,2), FHRS NUMBER(6,2), SHRS NUMBER(6,2), UHRS NUMBER(6,2), FK_TCTC_ID NUMBER(8) NOT NULL, FK_TC_CNBRTCCN_ID NUMBER(8) NOT NULL)
Sample Data:
Insert into TC
(TC_ID,STRT_DT,END_DT,STAT_CD,FULL_WK_FL,VERIF_LST_DT,VERIF_WRN_CNT,VERIF_ERR_CNT,SUBL_DT,SUBL_LATE_FL,SUBL_LAST_DT,SUBR_E_SGNR,SUBR_AUD_STR,ORIG_LATE_FL,APRV_DT,APRV_LATE_FL,D_APVR_ID,D_APVR_TYP,APVR_E_SGNR,APVR_AUD_STR,REJ_DT,LST_MOD_USERID,LST_MOD_DT,LST_MOD_ACTN,TC_TOT_HRS,TC_MHRS,TC_THRS,TC_WHRS,TC_HHRS,TC_FHRS,TC_SHRS,TC_UHRS,FK_TC_WKRTCW_TYP,FK_TC_WKRTCW_ID,POST_ID,POST_DT,POST_TYP,POST_SIG)
values (75092,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),'Approved
','Y',to_timestamp('19-MAR-09','DD-MON-RR HH.MI.SSXFF AM'),
3,0,to_timestamp('13-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),'Y',to_timestamp('19-MAR-09','DD-MON-RR HH.MI.SSXFF
AM'),'marichli.gif ','A.S. Not
Available ',null,to_timestamp('28-MAR-09','DD-MON-RR HH.MI.SSXFF
AM'),'Y',6800,'E','whenry.gif
','A.S. Not Available ',to_timestamp('17-MAR-09','DD-MON-RR
HH.MI.SSXFF AM'),'6800 ',to_timestamp('28-MAR-09','DD-MON-RR
HH.MI.SSXFF AM'),'Approved ',46.5,9.5,9.5,9,9,9.5,0,0,'E',
7710,null,null,null,null);
Insert into TC
(TC_ID,STRT_DT,END_DT,STAT_CD,FULL_WK_FL,VERIF_LST_DT,VERIF_WRN_CNT,VERIF_ERR_CNT,SUBL_DT,SUBL_LATE_FL,SUBL_LAST_DT,SUBR_E_SGNR,SUBR_AUD_STR,ORIG_LATE_FL,APRV_DT,APRV_LATE_FL,D_APVR_ID,D_APVR_TYP,APVR_E_SGNR,APVR_AUD_STR,REJ_DT,LST_MOD_USERID,LST_MOD_DT,LST_MOD_ACTN,TC_TOT_HRS,TC_MHRS,TC_THRS,TC_WHRS,TC_HHRS,TC_FHRS,TC_SHRS,TC_UHRS,FK_TC_WKRTCW_TYP,FK_TC_WKRTCW_ID,POST_ID,POST_DT,POST_TYP,POST_SIG)
values (75522,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),'Approved
','Y',to_timestamp('19-MAR-09','DD-MON-RR HH.MI.SSXFF AM'),
4,0,to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),'N',to_timestamp('19-MAR-09','DD-MON-RR HH.MI.SSXFF
AM'),'marichli.gif ','A.S. Not
Available ',null,to_timestamp('28-MAR-09','DD-MON-RR HH.MI.SSXFF
AM'),'Y',6800,'E','whenry.gif
','A.S. Not Available ',to_timestamp('17-MAR-09','DD-MON-RR
HH.MI.SSXFF AM'),'6800 ',to_timestamp('28-MAR-09','DD-MON-RR
HH.MI.SSXFF AM'),'Approved ',44,10,8,8,8,8,0,2,'E',
7710,null,null,null,null);
CREATE TABLE TIMECARD.TC
(
TC_ID NUMBER(8) NOT NULL, STRT_DT DATE, END_DT DATE, STAT_CD CHAR(10 BYTE), FULL_WK_FL CHAR(1 BYTE), VERIF_LST_DT DATE, VERIF_WRN_CNT NUMBER(4), VERIF_ERR_CNT NUMBER(4), SUBL_DT DATE, SUBL_LATE_FL CHAR(1 BYTE), SUBL_LAST_DT DATE, SUBR_E_SGNR CHAR(50 BYTE), SUBR_AUD_STR CHAR(24 BYTE), ORIG_LATE_FL CHAR(1 BYTE), APRV_DT DATE, APRV_LATE_FL CHAR(1 BYTE), D_APVR_ID NUMBER(8), D_APVR_TYP CHAR(1 BYTE), APVR_E_SGNR CHAR(50 BYTE), APVR_AUD_STR CHAR(24 BYTE), REJ_DT DATE, LST_MOD_USERID CHAR(8 BYTE), LST_MOD_DT DATE, LST_MOD_ACTN CHAR(12 BYTE), TC_TOT_HRS NUMBER(6,2), TC_MHRS NUMBER(6,2), TC_THRS NUMBER(6,2), TC_WHRS NUMBER(6,2), TC_HHRS NUMBER(6,2), TC_FHRS NUMBER(6,2), TC_SHRS NUMBER(6,2), TC_UHRS NUMBER(6,2), FK_TC_WKRTCW_TYP CHAR(1 BYTE) NOT NULL, FK_TC_WKRTCW_ID NUMBER(8) NOT NULL, POST_ID NUMBER(8), POST_DT DATE, POST_TYP CHAR(4 BYTE), POST_SIG VARCHAR2(50 BYTE)
)
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75092,1);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75092,29);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75092,310);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75092,740);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75092,1104);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75092,2617);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
4,0,1,1,1,1,0,0,75092,3115);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
7.5,0.5,1,2,2,2,0,0,75092,3141);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
7.5,0.5,1,2,2,2,0,0,75092,3147);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
27.5,8.5,6.5,4,4,4.5,0,0,75092,3149);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('05-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('11-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75092,3227);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75522,1);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75522,29);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75522,740);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75522,1104);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
0,0,0,0,0,0,0,0,75522,2617);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
8,3,1,2,1,1,0,0,75522,3115);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
9,2,1,2,2,2,0,0,75522,3141);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
8,3,1,2,1,1,0,0,75522,3147);
Insert into TC_CN_LN
(TCCNL_SNBR,D_STRT_DT,D_END_DT,TOT_HRS,MHRS,THRS,WHRS,HHRS,FHRS,SHRS,UHRS,FK_TCTC_ID,FK_TC_CNBRTCCN_ID)
values (0,to_timestamp('12-JAN-09','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('18-JAN-09','DD-MON-RR HH.MI.SSXFF AM'),
19,2,5,2,4,4,0,2,75522,3149);
Received on Mon Oct 19 2009 - 14:11:16 CDT