Tablas Pivote Oracle

Las tablas pivote sirven para presentar los datos de una forma mas estructurada, reportes, mostrar totales etc, a modo de darle una mejor vista a los datos y que sean mas entendibles al mismo tiempo.

Esto se puede realizar con sql básico que es aceptado por cualquier DBMS y también haciendo uso de la función "pivot" que no pertenece al estándar sql pero que es aceptada por algunos dbms.

Oracle implemento el uso de esta función a partir de la versión 11 (según leí) por lo que en versiones anteriores no es reconocida, se usaba la función DECODE que trabaja de forma similar.

Los siguientes ejemplos están realizados en oracle 11.2 XE versión para windows y prácticamente sirven para mostrar como pasar una consulta pivote realizada con sql básico a una realizada con la función pivot, la cual nos simplifica el código de la consulta.

Se tiene la siguiente consulta con tabla pivote realizada con sql básico

select extract(year from fecha_inventario), SUM(case when extract(month from fecha_inventario)=1 then 1 else 0 end) ENERO, SUM(case when extract(month from fecha_inventario)=2 then 1 else 0 end) febrero, SUM(case when extract(month from fecha_inventario)=3 then 1 else 0 end) marzo, SUM(case when extract(month from fecha_inventario)=4 then 1 else 0 end) abril, SUM(case when extract(month from fecha_inventario)=5 then 1 else 0 end) mayo, SUM(case when extract(month from fecha_inventario)=6 then 1 else 0 end) junio, SUM(case when extract(month from fecha_inventario)=7 then 1 else 0 end) julio, SUM(case when extract(month from fecha_inventario)=8 then 1 else 0 end) agosto, SUM(case when extract(month from fecha_inventario)=9 then 1 else 0 end) septiembre, SUM(case when extract(month from fecha_inventario)=10 then 1 else 0 end) octubre, SUM(case when extract(month from fecha_inventario)=11 then 1 else 0 end) noviembre, SUM(case when extract(month from fecha_inventario)=12 then 1 else 0 end) diciembre, count(fecha) as subtotal from EQUIPOS group by extract(year from fecha_inventario) order by extract(year from fecha_inventario)

Para realizar esta misma consulta con la función pivot se realiza lo siguiente :

1. Seleccionamos los campos que necesitamos en este caso el año y el mes donde cada año representa una fila y los meses las columnas.

Select * (Select extract(year from fecha_inventario) as anio,extract(month from fecha_inventario) as mes From equipos S )

2. Escribir la instrucción pivot a continuación de la consulta anterior

3. Hacer la función de agregación que necesitamos en este caso “count” y realizar el FOR y el IN

Pivot ( Count (mes) For mes in (1 as enero,2 as febrero,3 as marzo,4 as abril,5 as mayo,6 as junio,7 as julio,8 as agosto,9 as septiembre,10 as octubre,11 as noviembre,12 as diciembre) ) order by anio

Count (mes) para contar el numero de equipos por cada mes y luego el FOR<campo>  en este caso también el mes porque en el es donde vamos a buscar los valores que necesitamos y por ultimo IN en donde se le da un listado de valores a buscar.

Como se puede observar al encontrar un “1” se devolverá “enero” y así sucesivamente.

3. Con la consulta anterior todo bien, y hay muchos ejemplos así en Internet pero el problema es que nos falta la columna “TOTAL” que tiene la consulta mostrada al inicio. Para esto en vez de tener select * al principio de la consulta agregamos lo siguiente

Select anio,enero,febrero,marzo,abril,mayo,junio,julio,agosto,septiembre,octubre,noviembre,diciembre, enero+febrero+marzo+abril+mayo+junio+julio+agosto+septiembre+octubre+noviembre+diciembre as total from…. 

 Donde sumamos todos los campos para obtener el total y así tener otra columna al final.

La consulta nos queda entonces:


Select anio,enero,febrero,marzo,abril,mayo,junio,julio,agosto,septiembre,octubre,noviembre,diciembre, enero+febrero+marzo+abril+mayo+junio+julio+agosto+septiembre+octubre+noviembre+diciembre as total from (Select extract(year from fecha_inventario) as anio,extract(month from fecha_inventario) as mes From equipos S ) Pivot ( Count (mes) For mes in (1 as enero,2 as febrero,3 as marzo,4 as abril,5 as mayo,6 as junio,7 as julio,8 as agosto,9 as septiembre,10 as octubre,11 as noviembre,12 as diciembre) ) order by anio

y con esto ya esta realizada la consulta con la función PIVOT.

Nota: al incluir la clausula IN solamente se puede indicar un conjunto de valores, es decir un listado previamente conocido de valores ya que si queremos agregar las columnas de forma dinámica haciendo un SELECT IN nos dará un error ya que la función pivot no permite trabajar de esta manera.

Por ultimo un ejemplo de una consulta que agrega totales verticales y lo unico que se hace es un UNION ALL.

select * from( Select anio,particion as "1 Particion",particiones2 as "2 Particiones",Particiones3 as "3 particiones", Particiones4 as "4 Particiones", particiones as "+ Particiones", particion+particiones2+particiones3+particiones4+particiones as total From (Select TO_CHAR(extract(year from eq.fecha_inventario)) as anio,asig.particiones as particion From asig_equipos_disco asig,equipos eq ,modelos_disco modelo where eq.idespacio =asig.idespacio and eq.codigo = asig.codigo and asig.idmodelo = modelo.idmodelo ) X Pivot ( Count (particion) For particion in (1 as Particion,2 as Particiones2,3 as Particiones3 ,4 as Particiones4, 5 as Particiones) ) UNION ALL Select total,p1,p2,p3,p4,p5, p1+p2+p3+p4+p5 From (SELECT (select 'TOTAL' from dual) as total,particiones as particion From asig_equipos_disco ) X Pivot ( Count (particion) For particion in (1 as p1,2 as p2,3 as p3,4 as p4,5 as p5) ) ) order by anio