9 Replies Latest reply: May 2, 2014 5:23 AM by oliver421 RSS

    Pivot functionality in oracle 11 g .

    oliver421


      hi ,

      i have a table where in i have values as   

      tpea_value_trn

       

      ,tpea_value_fxn,

      tpea_value_grp  coresponding to tpea_period and tpea_fdr_tran_no , tpea_value_desc

       

      i want to pivot and display the values of the txn corresponding to each period .

      i used the following :

       

      select * from

      ( select tpea_fdr_tran_no , tpea_value_desc , tpea_period , tpea_value_trn from

      hpl_trade)

      pivot(sum(tpea_value_trn) for (tpea_period)in ( 'M' as mtd_txn , 'Y' as ytd_txn , 'Q' as qtd_txn));

      i get the results  with columns:

      tpea_fdr_tran_no , tpea_value_desc , mtd_txn, ytd_txn,qtd_txn.

       

      now i want pivot for  fxn and grp values also. however on using the following query this is not working :

       

       

      select * from

      ( select tpea_fdr_tran_no , tpea_value_desc , tpea_period , tpea_value_trn from

      hpl_trade)

      pivot(sum(tpea_value_trn) for (tpea_period)in ( 'M' as mtd_txn , 'Y' as ytd_txn , 'Q' as qtd_txn)),

      pivot(sum(tpea_value_fxn) for (tpea_period)in ( 'M' as mtd_fxn , 'Y' as ytd_fxn , 'Q' as qtd_fxn)),

      pivot(sum(tpea_value_grp) for (tpea_period)in ( 'M' as mtd_grp , 'Y' as ytd_grp , 'Q' as qtd_grp));

       

       

      can anyone please help on this ?