PIVOT is a SQL operation, introduced in Oracle 11g, that lets you write cross tabulation (also called transposed, crosstab and matrix) queries that rotate rows into columns while aggregating data in the rotation process.An overview of the new syntax is as follows:

SELECT ...
FROM   ...
PIVOT [XML]
   ( pivot_clause
     pivot_for_clause
     pivot_in_clause )
WHERE  ...

In addition to the new PIVOT keyword, we can see three new pivot clauses, described below.

pivot_clause: defines the columns to be aggregated (pivot is an aggregate operation);
pivot_for_clause: defines the columns to be grouped and pivoted;
pivot_in_clause: defines the filter for the column(s) in the pivot_for_clause (i.e. the range of values to limit the results to). The aggregations for each value in the pivot_in_clause will be transposed into a separate column (where appropriate).

The syntax and mechanics of pivot queries will become clearer with some examples.

PIVOT XML:

WITH pivot_data AS
 (select cd_fornitore, cd_int, vl_costo, vl_costo_iper, cd_deposito 
    from of_ordini_fornitori t)
SELECT *
  FROM pivot_data 
  PIVOT (
      XML (
            sum(vl_costo_iper)  --<-- pivot_clause
            for cd_deposito     --<-- pivot_for_clause
            in (any)            --<-- pivot_in_clause
          )
        )
ORDER BY 1;

PIVOT EXAMPLE2:

WITH pivot_data AS
 (select cd_fornitore, cd_int, vl_costo, vl_costo_iper, cd_deposito 
    from of_ordini_fornitori t)
SELECT *
  FROM pivot_data 
  PIVOT 
      (
        sum(vl_costo_iper)        --<-- pivot_clause
        for (cd_deposito,cd_int)  --<-- pivot_for_clause
        in( 
           (95,'0350800465')
           (96,'0350800466')
           (97,'0350800467')
          )        --<-- pivot_in_clause
      )
ORDER BY 1;

PIVOT EXAMPLE3:

select * 
from
(
----
select df.ds_debito, scv.cd_sap, scv. cd_voce_contabile, scv.descr, 
       scv.cd_finanziario, scv.cd_casse_finanziario, 
       scv.descrizione, scv.cd_pdv, scv.ammontare 
from 
( 
select v.cd_sap, sc.*, v.descr 
from 
( 
select s.*, a.cd_voce_contabile, a.cd_casse_finanziario, a.descrizione 
from 
( 
select g.cd_soc, g.cd_finanziario, g.descrizione_imp, 
       sum(g.ammontare) ammontare, g.cd_pdv 

from 
( 
select sf.cd_soc, sf.cd_pdv, sf.dt_incasso, sf.cd_finanziario, sf.descrizione_imp, 
       sf.ammontare, sf.cd_tipomov 
 from MGI_STORICOFINANZIARI sf 
where sf.dt_incasso between '20100101' and '20101231' 
) g 
group by 
g.cd_soc, g.cd_pdv, g.cd_finanziario, 
g.descrizione_imp, g.cd_tipomov 
) s 
left join mgi_anagrafica_finanziari a 
on s.cd_finanziario=a.cd_finanziario 
) sc 
left join mgi_vocicontabili v 
on sc.cd_soc=v.cd_soc and sc.cd_pdv=v.cd_pdv 
                      and sc.cd_voce_contabile=v.cd_voce_cont 
) scv 
left join mgi_debiti_finanziari df 
on scv.cd_soc=df.cd_soc and scv.cd_sap=df.cd_sap
--order by scv.cd_soc, ds_debito, scv.cd_sap, scv.cd_finanziario, scv.cd_pdv
-----
)pvt

pivot
(
sum (ammontare)                       --<-- pivot_clause
for cd_pdv                            --<-- pivot_for_clause
in (1,2,3,4,5,6,7,9,10,12,14,15,19,20,21,22,
    23,24,25,26,27,28,29,31,8,16,18)  --<-- pivot_in_clause
) mypvt

order by ds_debito, cd_sap, cd_finanziario