Nvidal’s Blog

Just another WordPress.com weblog

Oracle Tips: Pivot Feature

Pivot

Muchas veces hemos querido hacer una salida de reporte tipo hoja electronica o crosstab pero el mismo no es posible sin la necesidad utilizar un motor o enging que recolecte la información y a través de vectores nos entreguen la información tabulada. Pues bien, la versión de Oracle 11g nos trae la opcion Pivot:

 

Como sabrás, las tablas relaciones son tabulares, o sea, son presentadas en columnas: Ej CUSTOMER

SQL> desc customers
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 CUST_ID                                            NUMBER(10)
 CUST_NAME                                          VARCHAR2(20)
 STATE_CODE                                         VARCHAR2(2)
 TIMES_PURCHASED                                    NUMBER(3)

 

When this table is selected:

 
select cust_id, state_code, times_purchased
from customers
order by cust_id;

The output is:

CUST_ID STATE_CODE TIMES_PURCHASED
------- ---------- ---------------
      1 CT                       1
      2 NY                      10
      3 NJ                       2
      4 NY                       4
... and so on ...

 

Note como los datos son presentados como filas: Para cada cliente, el registro presenta el estado donde vive cada cliente y cuantas veces el mismo ha comprado algo para esa tienda. Cuantas veces el cliente compra mas artículos, la columna Times_purchased es actualizada.

 

Ahora considerar el caso en donde se desee tener un reporte de compra frecuente en cada estado,  cuantos clientes compraron solo una vez, dos, tres …etc, en cada estado.  Para realizarlo en un SQL, usamos la siguiente declaración:

 
select state_code, times_purchased, count(1) cnt
from customers
group by state_code, times_purchased;

 

Here is the output:

ST TIMES_PURCHASED        CNT
-- --------------- ----------
CT               0         90
CT               1        165
CT               2        179
CT               3        173
CT               4        173
CT               5        152
... and so on ...

 

Esta es la informacion que se necesitaba pero es un poco difícil de leer. Una mejor forma es representar la misma data a través de un reporte crosstab, el cual pueda organizar la data verticalmente y el estado horizontal, tal como lo haríamos en una hoja electrónica.

 
Times_purchased
             CT           NY         NJ      ... and so on ...
 
1             0            1          0      ...
2            23          119         37      ...
3            17           45          1      ...
... and so on ... 

 

Anterior a Oracle 11g, podias hacerlo outilizando algo como la función decode para cada valor y escribir cada valores distintos en columnas separadas. Tecnica obsoleta después de 11g, pues contamos con una nueva característica llamada PIVOT para presentar cualquier búsqueda en un formato crosstab. Ej.

 
select * from (
   select times_purchased, state_code
   from customers t
)
pivot 
(
   count(state_code)
   for state_code in ('NY','CT','NJ','FL','MO')
)
order by times_purchased
/

Here is the output:

 
. TIMES_PURCHASED       'NY'       'CT'       'NJ'       'FL'       'MO'
--------------- ---------- ---------- ---------- ---------- ----------
              0      16601         90          0          0          0
              1      33048        165          0          0          0
              2      33151        179          0          0          0
              3      32978        173          0          0          0
              4      33109        173          0          1          0
... and so on ...

 

Esto presenta el poder del operador PIVOT. El state_code son presentado como el registro encabezado, en vez de una columna.

 

En un reporte crosstab, si desea transportar la columna del tiempo de compra al encabezado la columna se convierte en los registros, como si la columna se rotaran 90 grados.  

 

This expression needs to be in the syntax of the query:

...
pivot 
(
   count(state_code)
   for state_code in ('NY','CT','NJ','FL','MO')
)
...

 

La segunda line, “for state_code …,” limita la busqueda a solo esos valores. Esta línea es necesaria, pues desafortunadamente tienes que poner un limite. Esta restricción no se da en XML.

Note the header rows in the output:

. TIMES_PURCHASED       'NY'       'CT'       'NJ'       'FL'       'MO'
  --------------- ---------- ---------- ---------- ---------- ----------

La columna que forma el encabezado son data de la misma tabla: El state_code. Suspongamos que se desee presentar el nombre del estado y no la abreviación.

 (“Connecticut” instead of “CT”)?  en este caso se hace un pequeño ajuste en la clausula FOR:

 
select * from (
   select times_purchased as "Puchase Frequency", state_code
   from customers t
)
pivot 
(
   count(state_code)
   for state_code in ('NY' as "New York",'CT' "Connecticut",'NJ' "New Jersey",'FL' "Florida",'MO' as "Missouri")
)
order by 1
/
 
Puchase Frequency   New York Connecticut New Jersey    Florida   Missouri
----------------- ---------- ----------- ---------- ---------- ----------
                0      16601         90           0          0          0
                1      33048        165           0          0          0
                2      33151        179           0          0          0
                3      32978        173           0          0          0
                4      33109        173           0          1          0
... and so on ...

marzo 17, 2009 - Posted by | Developer, Oracle, Tecnologia

1 comentario »


Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: