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
Datos insertados en la tabla ejemplo
SQL de la forma tradicional
Resultado
SQL clausula PIVOT
Resultado
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:
Resultado
Explicacion de contexto de la clausula PIVOT
Sintaxis
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
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
| PERIODO | VENDEDOR | FACTURA | VALOR |
| 201701 | MANUEL | 3 | 4000 |
| 201701 | RAFAEL | 2 | 8000 |
| 201701 | VICTOR | 1 | 5000 |
| 201702 | MANUEL | 7 | 6000 |
| 201702 | RAFAEL | 4 | 3000 |
| 201702 | RAFAEL | 5 | 7000 |
| 201702 | VICTOR | 6 | 2000 |
| 201703 | MANUEL | 8 | 5000 |
| 201703 | MANUEL | 9 | 1000 |
| 201703 | RAFAEL | 10 | 4000 |
| 201703 | RAFAEL | 11 | 2000 |
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
| VENDEDOR | 201701 | 201702 | 201703 |
| VICTOR | 5000 | 2000 | 0 |
| MANUEL | 4000 | 6000 | 6000 |
| RAFAEL | 8000 | 10000 | 6000 |
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' |
| VICTOR | 5000 | 2000 | |
| MANUEL | 4000 | 6000 | 6000 |
| RAFAEL | 8000 | 10000 | 6000 |
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
| VENDEDOR | 201701'_MNT_FAC | 201701'_CNT_FAC | 201702'_MNT_FAC | 201702'_CNT_FAC | 201703'_MNT_FAC | 201703'_CNT_FAC |
| VICTOR | 5000 | 1 | 2000 | 1 | 0 | |
| MANUEL | 4000 | 1 | 6000 | 1 | 6000 | 2 |
| RAFAEL | 8000 | 1 | 10000 | 2 | 6000 | 2 |
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)
)
|
