Pivot en Oracle 11g

Post by: sistekeladmin
2019-08-14 10:31:20
0 Comentarios

Cuando deseamos convertir los resultados de nuestras consultas de filas a columnas necesitamos hacer uso de la claúsula Pivot de Oracle, de forma que aprenderemos la sintaxis esencial para aprender a manipular la claúsula.

En mi caso estoy usando Oracle 11g Enterprise y mi entorno de desarrollo integrado Oracle SQL Developer. La sintaxis de Pivot en Oracle es la siguiente:

Select * from table t                −> Origen de datos
Pivot                                −> Operador Pivot
( fn_agregada_1() Alias_1,
  Fn_agregada_2() Alias_2,           −> Funciones agrupadas a mostrar
  Fn_agregada_n () Alias_n
	
	For (campo_1 Alias_1 ,
	     Campo_2 Alias_2 ,…,     −> Campos donde se genera el pivot
	     Campo_n Alias_n) 
	     In(Lista de valores)    −> Filtros para generar las columnas
	)

Fuente: https://www.oracle.com/technetwork/es/articles/sql/nuevo-operador-pivot-oracle-11g-r2-1605402-esa.html

Ejercicio con pivot en oracle

Para nuestro ejemplo hemos creado un procedimiento almacenado llamado USP_VENTASEMPLEADO_TODOSMESES que nos permite visualizar las ventas de cada empleado en los meses del año.

/*reporte de todos los meses ventas por boleta del empleado*/
CREATE OR REPLACE PROCEDURE USP_VENTASEMPLEADO_TODOSMESES
(C_LISTA OUT SYS_REFCURSOR)
AS

  v_sql LONG       := NULL;
  v_statement LONG := NULL;
  NUEVAFECHA VARCHAR(50);
  SP_EXCEPTION EXCEPTION;
BEGIN
  
  v_sql := 'SELECT *FROM (
select (E.NOMBRE ||'||CHR(39)||CHR(32)||CHR(39)||'||E.APELLIDOS) AS EMPLEADO,EXTRACT(MONTH FROM FECHA) AS MES,COUNT(*) AS VECES from 
USUARIO_DBA_EXOTICA.BOLETA B INNER JOIN USUARIO_DBA_EXOTICA.EMPLEADO E ON E.IDEMPLEADO=B.IDEMPLEADO group by 
EXTRACT(MONTH FROM FECHA),(E.NOMBRE ||'||CHR(39)||CHR(32)||CHR(39)||'||E.APELLIDOS)
HAVING COUNT(*)>0
) PIVOT (SUM(VECES) FOR MES IN ('||chr(39)||1||chr(39)||'Enero,'||chr(39)||2||chr(39)||'Febrero,'||chr(39)||3||chr(39)||'Marzo,'||chr(39)||4||chr(39)||'Abril,'||chr(39)||5||chr(39)||'Mayo,'||chr(39)||6||chr(39)||'Junio,'||chr(39)||7||chr(39)||'Julio,'||chr(39)||8||chr(39)||'Agosto,'||chr(39)||9||chr(39)||'Setiembre,'||chr(39)||10||chr(39)||'Octubre,'||chr(39)||11||chr(39)||'Noviembre,'||chr(39)||12||chr(39)||'Diciembre))';
  
    OPEN C_LISTA 
    FOR v_sql; 
    
  EXCEPTION 
WHEN NO_DATA_FOUND THEN 

DBMS_OUTPUT.PUT_LINE('NO EXISTEN REGISTROS');
WHEN SP_EXCEPTION THEN
DBMS_OUTPUT.PUT_LINE ('NO SE ENCONTRO VENTAS DE  BOLETAS DE LOS EMPLEADOS POR LOS MESES');
END;

Nuestro procedimiento almacenado tiene una variable de tipo SYS_REFCURSOR como parámetro de salida. En nuestra consulta seleccionamos los nombres de los empleados, extraemos el número del mes según la fecha obtenida de la tabla boleta y contamos las veces que cada empleado a vendido agrupándolos por el número del mes y sus nombres.

Al realizar el pivot colocamos la función agregada al campo veces y que vaya sumándose, para la cabecera indicamos el campo MES y los filtramos por el número de mes, pero cada mes tendrá un alias para que nos deje de aparecer solo los números en la cabecera.

Cuando tenemos lista nuestra consulta aperturamos el cursor y le indicamos que se usará la variable que contiene la consulta.

Si en caso se produce alguna excepción se escribirá en la salida de DBMS de SQL Developer el mensaje respectivo.

Ejecución del procedimiento almacenado

Cuando ejecutemos nuestro procedimiento almacenado lo haremos de la siguiente manera:

SET SERVEROUTPUT ON;
VAR MYCURSOR REFCURSOR;
EXEC USP_VENTASEMPLEADO_TODOSMESES(:MYCURSOR)
PRINT MYCURSOR;

Activamos la salida de dbms en sql developer y creamos una variable de tipo REFCURSOR para que indicarle que será el parámetro que se enviará al procedimiento almacenado. Finalmente imprimimos el resultado de MYCURSOR.

Resultados del script ejecutado

Cuando ejecutamos nos debería salir un resultado como el siguiente:

En la columna empleado nos aparecerá la lista de empleados y en las columnas que siguen la cantidad de ventas por boletas que han realizado cada uno de ellos según el mes.