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

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) )