Pivot Table, Work ???

[oliveira.tadeu]

I created a view with the Sequents fields:

Code, reference, value;

Performing a simple query Returns some data:

Select all

 
CODIGO    REFERENCIA      VALOR 
---------------------------------------- 
AB6739	11/2017	        933,46 
AB6858	12/2017	        933,46 
AB2486	01/2018	        933,46 

I tried to execute the query below that I have seen in an example in Web:

Select all

 
select * from table(pivot('select * from nome_da_view')); 

The objective would be to return the data as follows:

Select all

 
CODIGO  11/2017     12/2017      01/2018 
-------------------------------------------------- 
AB6739  933,46 
AB6858                    933,46 
AB2486                                      933,46 

instead of the above result it returns the error:

Select all

ORA-00904 - "PIVOT": identificador inválido.

Follow the link where I got the example above: https://technology.amis.nl/2006/05/24/d ... s-thunder/
Please, someone helps me, I'm already beginning to believe that this pivot in Oracle is a farce.

[DanielNN]

Hello,
Oracle function was introduced in version 11.
This example does not use the Oracle Pivot function. It has the construction of the function below.

If your version is 11 or higher can look at: http://www.oracle.com/technetwork/pt/ar ... 3-ptb.html http://www.oracle-developer.net/display.php?id=506 https://oracle-base.com/articles/11g/pi ... tors-11gr1

[oliveira.tadeu]

Good morning, I saw the examples but as the name of the columns will be variable from the reference date did not work because the examples all bring the names of the fixed columns.

I have tried with the query below but also did not succeed because it displays two error messages and still do not know how to extract the data from the clob column with the XML code.

Select all

 
select distinct * from 
( 
select tcibem.codbem, 
       tcibem.descrabrev, 
       tcibem.numnota, 
       nvl(ad_depacel.saldo, 0) as saldo, 
       tcibem.dtfimdep, 
       tcitax.taxa, 
       tcibem.vlrsaldo, 
       saldos.referencia, 
       nvl(saldos.vlr_depreciado, 0) as vlr_depreciado 
  from tcibem, 
       ad_depacel, 
       tgfpro, 
       tcitax, 
       ( 
       select  tcisal.codprod as codprod 
       ,       tcisal.codbem  as codbem 
       ,       to_char(tcisal.referencia, 'mm/yyyy') as referencia 
       ,       tcisal.saldo as vlr_depreciado 
       from    tcisal 
       ) saldos  
 where ad_depacel.codbem (+) = tcibem.codbem  
   and tgfpro.codprod        = tcibem.codprod  
   and tcitax.codprod        = tcibem.codprod  
   and tcibem.codbem         <> '<TODOS>' 
   and tcibem.numnota        is not null 
   and saldos.referencia     is not null 
   and saldos.codprod    (+) = tcibem.codprod  
   and saldos.codbem     (+) = tcibem.codbem 
) 
pivot xml (sum(nvl(vlr_depreciado, 0)) as vlr_depreciado for (referencia) in (select to_char(referencia, 'mm/yyyy') as referencia from tcisal))   

[DanielNN]

Well,
One way to extract XML data would be like: https://stackoverflow.com/questions/368 ... l-xml-data
But which also forces you to define which data you want to return.
As your need is to be totally dynamic, as far as I know, Oracle's standard pivot does not answer.
But I researched here and his original source looks like it solves. However, you need to create the types and functions that are given at the end of the article in "How It Works" and "Resources". https://technology.amis.nl/2006/05/24/d ... s-thunder/