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

Read More

Leer Datos De Postgresql con Java

La siguiente mini aplicación muestra como conectarse a una base de datos ingresando de forma manual los parámetros de conexión (bd, puerto, etc) y luego visualizar los datos de cualquiera de las tablas de la BD seleccionada, lo diferente que tiene a la mayoría de ejemplos mostrados en Internet es que se cargan las columnas de forma dinámica  es decir que se cargan todas las columnas de la tabla y no solo un numero estático de columnas definidas en el programa, lo cual puede servir en muchos casos, con esto se tiene la conexión y la lectura de datos, si se quiere hacer cualquier otro uso como insertar , eliminar etc, se puede seguir el mismo patrón partiendo de este ejemplo.

En el .zip va incluido el driver (ultima version) para conectar java con postgres, se realizo en netbeans 7.3 con jdk 7u21.

La aplicación se ve así:

y en el cuadro de dialogo ingresan los valores correspondientes y en el combobox de la ventana principal pueden ver las tablas disponibles y ver los datos de cada una.

Pueden descargarla en: Java - Postgres
Nota: El código no es el mas optimo que digamos porque no tenia tiempo para mejorarlo, la prioridad era que funcionara XD y lo pueden notar en los métodos de la clase conexión pero puede ser mejorado y/o modificada de acuerdo a las necesidades.

Créditos a:  http://gnucesar.wordpress.com/2008/07/22/conexion-a-postgres-desde-java/ ya que a partir de esta clase me base para hacer la conexión.

Read More