viernes, 21 de julio de 2017

encolumnar resultados de un select con PIVOT

Oracle PIVOT

Desde la version 11 de Oracle es posible encolumnar los resultados totalizados de manera mas facil con la clausula PIVOT. En version 10 o anterior, era necesario realizar artificios con el decode o case when.

Veamos un ejemplo para explicar mejor. Tenemos una tabla con contiene las ventas realizadas en cada periodo por cada vendedor y deseamos presentar un resultado que muestre el resumen de las ventas presentando en la fila el vendedor y en las columnas los totales de ventas por mes.

Ejemplo

Definición de la tabla ejemplo
CREATE TABLE VENTAS (
PERIODO     VARCHAR2(6),
VENDEDOR    VARCHAR2(20),
FACTURA     NUMBER(5),
VALOR       NUMBER);


Datos insertados en la tabla ejemplo
PERIODOVENDEDORFACTURAVALOR
201701MANUEL34000
201701RAFAEL28000
201701VICTOR15000
201702MANUEL76000
201702RAFAEL43000
201702RAFAEL57000
201702VICTOR62000
201703MANUEL85000
201703MANUEL91000
201703RAFAEL104000
201703RAFAEL112000

SQL de la forma tradicional

SELECT  VENDEDOR,
            sum(DECODE(PERIODO,'201701',VALOR,0))  AS "201701",
            sum(DECODE(PERIODO,'201702',VALOR,0)) AS "201702",
            sum(DECODE(PERIODO,'201703',VALOR,0)) AS "201703"
FROM VENTAS GROUP BY VENDEDOR


Resultado
VENDEDOR201701201702201703
VICTOR500020000
MANUEL400060006000
RAFAEL8000100006000

SQL clausula PIVOT


SELECT
FROM (
   SELECT VENDEDOR, PERIODO,VALOR
   FROM VENTAS
)
PIVOT
(
   SUM(VALOR)  
   FOR (PERIODO) IN ('201701','201702','201703')
)


Resultado
VENDEDOR'201701''201702''201703'
VICTOR50002000
MANUEL400060006000
RAFAEL8000100006000


Como se nota, tenemos el mismo resultado pero de manera mas facil, digamos que necesitamos incluir un total adicional que es numero de factura por vendedor, lo que hacemo es agregar la funcion agregada COUNT:
SELECT * FROM (
   SELECT VENDEDOR, PERIODO,VALOR
   FROM VENTAS
)
PIVOT
(
   SUM(VALOR) MNT_FAC,--Suma el monto de las facturas
   COUNT(1)   CNT_FAC --Cunta las facturas
   FOR (PERIODO) IN ('201701','201702','201703')
)


Resultado
VENDEDOR201701'_MNT_FAC201701'_CNT_FAC201702'_MNT_FAC201702'_CNT_FAC201703'_MNT_FAC201703'_CNT_FAC
VICTOR5000120001
0
MANUEL400016000160002
RAFAEL8000110000260002

Explicacion de contexto de la clausula PIVOT
Sintaxis
SELECT * FROM

--Los campos y tablas que vamos a usar en la consulta, tanto los agupados como los totalizados

  SELECT column1, , column2, column3
  FROM tables
  WHERE conditions
–Filtro de datos
)
PIVOT
(
  Funcion agregada(column2)
–Funciones de agrgacion SUM, COUNT, AVG, MAX, MIN
  FOR column1
  IN ( expr1, expr2, ... expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];
 

 Parametros y argumentos
 Funcion agregada: Se indica la funciones agregadas que se usaran SUM, COUNT, MIN, MAX o AVG

IN: Se especifican las expresiones  que contiene column2 para presentar en columnas, en nuestro ejemplo eran los datos del campo PERIODO enlistados en 201701, 201702 y '201703.
Tambien es posible ubicar un subquery para listar las expresiones, pero solo es posible con PIVOT XML, el resultado arrojado sera un XML.

PIVOT XML

SELECT * FROM (
   SELECT VENDEDOR, PERIODO,VALOR
   FROM VENTAS
)
PIVOT XML
(
   SUM(VALOR) MNT_FAC
   FOR (PERIODO) IN (SELECT TO_CHAR(ADD_MONTHS(SYSDATE,(LEVEL-1)*-1),'yyyymm')PERIODO FROM DUAL CONNECT BY LEVEL <=7)
)


No hay comentarios:

Publicar un comentario