Trasponer tabla en Oracle con cláusula PIVOT

La versión 11g de la base de datos Oracle incorpora la cláusula PIVOT que resuelve, en parte, un problema que alguna vez se me presentó y que tuve que resolver con una consulta poco intuitiva y limitada.

Paso a explicar el tema en detalle con el siguiente ejemplo:

Supongamos una tabla donde se guarde el total de las ventas mensuales por distintas compañías:


[SQL]
drop table test;

create table test (
company varchar(12),
monthy char(7),
sales number
);

insert into test values ('CMP1','2008-06', 10);
insert into test values ('CMP1','2008-07', 29);
insert into test values ('CMP1','2008-08', 39);
insert into test values ('CMP1','2008-09', 41);
insert into test values ('CMP1','2008-10', 22);

insert into test values ('CMP2','2008-06', 13);
insert into test values ('CMP2','2008-07', 17);
insert into test values ('CMP2','2008-08', 61);
insert into test values ('CMP2','2008-09', 55);
insert into test values ('CMP2','2008-10', 71);

insert into test values ('CMP3','2008-06', 33);
insert into test values ('CMP3','2008-07', 18);
insert into test values ('CMP3','2008-08', 27);
insert into test values ('CMP3','2008-09', 5);
insert into test values ('CMP3','2008-10', 32);
[/SQL]

Para obtener lo totales de ventas por compañía, podemos escribir algo así:


[SQL]
SELECT company, monthy,SUM(sales)
FROM test
GROUP BY company, monthy
[/SQL]

Si nuestra intención es disponer estos datos de forma traspuesta, y obtener un listado donde:

podemos escribir algo así:


[SQL]
SELECT company,jul,ago,sep
FROM ( SELECT company,
SUM(case when monthy='2003-07' then sales else null end) jul,
SUM(case when monthy='2003-08' then sales else null end) ago,
SUM(case when monthy='2003-09' then sales else null end) sep
FROM test
GROUP BY company );
[/SQL]

Donde obtendríamos:


[CODE]
company jul ago sep
-------- ---------- ---------- ----------
CMP1 29 39 41
CMP2 17 61 55
CMP3 18 27 5
[/CODE]

Si bien, la consulta anterior funciona, no es muy intuitiva. Con la cláusula PIVOT podemos escribir la misma consulta de manera más sencilla y entendible:


[SQL]
SELECT *
FROM ( SELECT company, monthy, sales
FROM test ) S
PIVOT (SUM(sales) FOR monthy IN ('ABC123','DEF456','XYZ987'))
ORDER BY company;
[/SQL]

Esto es solo un comienzo, dejo a vuestra imaginación y uso todas las combinaciones posibles que podemos escribir con esta cláusula.

Vía: Programación PL/SQL

Tags: ,


  • HAPPYHACKING

    Hay un error en el query siguiente, no es 2003, es 2008

    este seria el correcto

    SELECT company,jul,ago,sep
    FROM ( SELECT company,
    SUM(case when monthy=’2008-07′ then sales else NULL end) jul,
    SUM(case when monthy=’2008-08′ then sales else NULL end) ago,
    SUM(case when monthy=’2008-09′ then sales else NULL end) sep
    FROM test
    GROUP BY company );

    Reply

  • Eu

    Fue de mucha utilidad el ejemplo que pusiste. Aun cuando cuento con oracle 10.x y no puedo usar la cláusula Pivot el procedimiento que describes me ayudó para lograr el objetivo.

    Saludos

    Reply

  • Mara

    Muchas gracias por la info.. muy valiosa..!!

    Reply

  • Edwin Espinoza

    Muchas gracias por la publicacion. Me sirvio de mucho baje un procesos de 8 horas a 2 horas.

    Saludos y continua con esta tarea loable.

    Reply

  • Ronaldinho

    q tal estimados he generado esta consulta pero me bota errores a q se debe…gracias

    select *
    from
    (
    select
    trabajador,
    concepto,
    monto_concepto
    from
    sisper.pa_planilla_calculada_mes
    where
    ano = ‘2012’ and mes = ’06’) es
    pivot
    (sum(monto_concepto) for concepto in (select concepto from
    sisper.pa_planilla_calculada_mes group by concepto))
    order by trabajador;
    pero el SQL Developer me bota el siguiente error:

    ORA-00936: falta una expresión
    00936. 00000 – “missing expression”
    *Cause:
    *Action:
    Error en la línea: 13, columna: 38

    Reply

  • Makenna

    Ronaldinho tu error esta en:

    pivot
    (sum(monto_concepto) for concepto in (select concepto from
    sisper.pa_planilla_calculada_mes group by concepto))
    order by trabajador;

    Dentro del IN no puedes poner una consulta, a fuerza deber poner las clausulas.
    Ese es un inconveniente al usar PIVOT, que tienes que conocer absolutamente todos tus casos.

    En este link lo explica –> http://www.oracle.com/technetwork/es/articles/sql/nuevo-operador-pivot-oracle-11g-r2-1605402-esa.html

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.