Home » SQL & PL/SQL » SQL & PL/SQL » Transpose columns (Oracle 11g)
| Transpose columns [message #640199] |
Thu, 23 July 2015 09:20  |
 |
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
HI,
I am having table like below
BM_PRODUCT_CODE EFF_FROM_DATE EFF_TO_DATE PRODUCT_FEE FEE_PAY_DATE_ADJ FREE_OD_BUFFER TIER_TYPE MIN_TIER_AMT MAX_TIER_AMT NOM_INT_RATE
143 25/03/1996 09/04/2000 0 1 T1 0 0 0
143 25/03/1996 09/04/2000 0 1 T2 1 5000 12.37
143 25/03/1996 09/04/2000 0 1 T4 26.41
143 10/04/2000 09/09/2001 0 1 T1 0 0 0
143 10/04/2000 09/09/2001 0 1 T2 1 5000 12.328
143 10/04/2000 09/09/2001 0 1 T4 26.4
143 10/09/2001 17/08/2008 0 1 T4 24.582
143 10/09/2001 17/08/2008 0 1 T1 0 0 0
143 10/09/2001 17/08/2008 0 1 T2 1 5000 12.328
143 18/08/2008 07/06/2009 0 1 T2 1 5000 12.328
143 18/08/2008 07/06/2009 0 1 T1 0 0 0
143 18/08/2008 07/06/2009 0 1 T4 0
143 08/06/2009 29/10/2009 0 1 T1 0 0 0
143 08/06/2009 29/10/2009 0 1 T4 0
143 08/06/2009 29/10/2009 0 1 T2 1 5000 15.76
143 30/10/2009 12/01/2011 0 1 T1 0 0 0
143 30/10/2009 12/01/2011 0 1 T2 1 5000 17.82
143 30/10/2009 12/01/2011 0 1 T4 0
143 13/01/2011 16/03/2015 0 1 T4 0
143 13/01/2011 16/03/2015 0 1 T2 1 5000 17.777
143 13/01/2011 16/03/2015 0 1 T1 0 0 0
I need to display like below Wherever in ref_table T4 is there that will have null MIN and MAX value and have only Nominal Int value
BM_PRODUCT_CODE EFF_FROM_DATE EFF_TO_DATE PRODUCT_FEE FEE_PAY_DATE_ADJ T1 MIN T1 MAX T1 NOM_INT_RATE T2 MIN T2 MAX T2 NOM_INT_RATE T4 NOM_INT_RATE
0143 25/03/1996 09/04/2000 0 1 0 0 0% 1 5000 12.370 26.410
0143 10/04/2000 09/09/2001 0 1 0 0 0% 1 5000 12.328 26.400
0143 10/09/2001 17/08/2008 0 1 0 0 0% 1 5000 12.328 24.582
0143 18/08/2008 07/06/2009 0 1 0 0 0% 1 5000 12.328 0.000
0143 08/06/2009 29/10/2009 0 1 0 0 0% 1 5000 15.760 0.000
0143 30/10/2009 12/01/2011 0 1 0 0 0% 1 5000 17.820 0.000
0143 13/01/2011 16/03/2015 0 1 0 0 0% 1 5000 17.777 0.000
Scripts
CREATE TABLE "REF_TABLE"
(
"BM_PRODUCT_CODE" NUMBER(4,0),
"EFF_FROM_DATE" DATE,
"EFF_TO_DATE" DATE,
"PRODUCT_FEE" NUMBER(10,0),
"FEE_PAY_DATE_ADJ" NUMBER(3,0),
"FREE_OD_BUFFER" NUMBER(10,0),
"TIER_TYPE" CHAR(2 BYTE),
"MIN_TIER_AMT" NUMBER(10,0),
"MAX_TIER_AMT" NUMBER(10,0),
"NOM_INT_RATE" NUMBER(6,3)
);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('25/03/1996','DD/MM/YYYY'),to_date('09/04/2000','DD/MM/YYYY'),0,1,null,'T1',0,0,0);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('25/03/1996','DD/MM/YYYY'),to_date('09/04/2000','DD/MM/YYYY'),0,1,null,'T2',1,5000,12.37);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('25/03/1996','DD/MM/YYYY'),to_date('09/04/2000','DD/MM/YYYY'),0,1,null,'T4',null,null,26.41);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('10/04/2000','DD/MM/YYYY'),to_date('09/09/2001','DD/MM/YYYY'),0,1,null,'T1',0,0,0);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('10/04/2000','DD/MM/YYYY'),to_date('09/09/2001','DD/MM/YYYY'),0,1,null,'T2',1,5000,12.328);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('10/04/2000','DD/MM/YYYY'),to_date('09/09/2001','DD/MM/YYYY'),0,1,null,'T4',null,null,26.4);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('10/09/2001','DD/MM/YYYY'),to_date('17/08/2008','DD/MM/YYYY'),0,1,null,'T4',null,null,24.582);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('10/09/2001','DD/MM/YYYY'),to_date('17/08/2008','DD/MM/YYYY'),0,1,null,'T1',0,0,0);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('10/09/2001','DD/MM/YYYY'),to_date('17/08/2008','DD/MM/YYYY'),0,1,null,'T2',1,5000,12.328);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('18/08/2008','DD/MM/YYYY'),to_date('07/06/2009','DD/MM/YYYY'),0,1,null,'T2',1,5000,12.328);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('18/08/2008','DD/MM/YYYY'),to_date('07/06/2009','DD/MM/YYYY'),0,1,null,'T1',0,0,0);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('18/08/2008','DD/MM/YYYY'),to_date('07/06/2009','DD/MM/YYYY'),0,1,null,'T4',null,null,0);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('08/06/2009','DD/MM/YYYY'),to_date('29/10/2009','DD/MM/YYYY'),0,1,null,'T1',0,0,0);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('08/06/2009','DD/MM/YYYY'),to_date('29/10/2009','DD/MM/YYYY'),0,1,null,'T4',null,null,0);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('08/06/2009','DD/MM/YYYY'),to_date('29/10/2009','DD/MM/YYYY'),0,1,null,'T2',1,5000,15.76);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('30/10/2009','DD/MM/YYYY'),to_date('12/01/2011','DD/MM/YYYY'),0,1,null,'T1',0,0,0);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('30/10/2009','DD/MM/YYYY'),to_date('12/01/2011','DD/MM/YYYY'),0,1,null,'T2',1,5000,17.82);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('30/10/2009','DD/MM/YYYY'),to_date('12/01/2011','DD/MM/YYYY'),0,1,null,'T4',null,null,0);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('13/01/2011','DD/MM/YYYY'),to_date('16/03/2015','DD/MM/YYYY'),0,1,null,'T4',null,null,0);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('13/01/2011','DD/MM/YYYY'),to_date('16/03/2015','DD/MM/YYYY'),0,1,null,'T2',1,5000,17.777);
Insert into REF_TABLE (BM_PRODUCT_CODE,EFF_FROM_DATE,EFF_TO_DATE,PRODUCT_FEE,FEE_PAY_DATE_ADJ,FREE_OD_BUFFER,TIER_TYPE,MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE) values (143,to_date('13/01/2011','DD/MM/YYYY'),to_date('16/03/2015','DD/MM/YYYY'),0,1,null,'T1',0,0,0);
|
|
|
|
 Re: Transpose columns [message #640240 is a reply to message #640199] |
Fri, 24 July 2015 10:40  |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
Try this:
SELECT *
FROM ( SELECT Bm_Product_Code
, Eff_From_Date
, Eff_To_Date
, Product_Fee
, Fee_Pay_Date_Adj
, Tier_Type
, Min_Tier_Amt
, Max_Tier_Amt
, Nom_Int_Rate
FROM Ref_Table )
PIVOT
(MIN ( Min_Tier_Amt ) AS Tier_Min
, MAX ( Max_Tier_Amt ) AS Tier_Max
, MAX ( Nom_Int_Rate ) AS Tier_Rate
FOR Tier_Type IN ('T1' AS T1, 'T2' AS T2, 'T4' AS T4))
ORDER BY 1, 2, 3
[Updated on: Fri, 24 July 2015 10:48] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Thu May 09 00:36:52 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
