Anuncios en tutorial de programación PLSQL

miércoles, 16 de septiembre de 2015

Oracle 11g y la cláusula PIVOT: Como girar columnas en SQL y PL/SQL

Una tarea que puede resultarnos útil en determinadas circunstancias es conseguir girar (en inglés pivot) los resultados entregados por una consulta SQL o PLSQL. Por ejemplo, supongamos que escribimos la siguiente consulta para calcular el precio medio de los productos por almacen:

SQL> SELECT almacen, producto, AVG(precio) pmedio
2  FROM  productos
3  GROUP BY almacen, producto
4  /

ALMACEN PRODUCTO PMEDIO
------- -------- ------
Ba      ABC123       95
Ba      DEF456       75
Ba      XYZ987      160
Ma      ABC123      100
Ma      XYZ987      150
Va      DEF456       80

6 rows selected.       

Basándonos en esta consulta, lo que queremos conseguir es que los distintos productos (ABC123, DEF456 y XYZ987) lleguen a ser las cabeceras de la consulta. Esto se puede conseguir modificando la consulta SELECT de la siguiente manera:

SQL> SELECT almacen,
2     AVG(DECODE(producto,'ABC123',precio)) ABC123,
3     AVG(DECODE(producto,'DEF456',precio)) DEF456,
4     AVG(DECODE(producto,'XYZ987',precio)) XYZ987
5   FROM productos
6   GROUP BY almacen
7   ORDER BY almacen
8   /

ALMACEN ABC123 DEF456 XYZ987
------- ------ ------ ------
Ba          95     75    160
Ma         100           150
Va                 80       

No obstante, aunque la consulta tal y como está escrita funciona, no podemos decir que el llegar a ella sea fácil o intuitivo. Para poder escribir de una manera más sencilla y entendible este tipo de consultas, la versión 11g de las bases de datos Oracle incorpora la nueva cláusula PIVOT. Veamos como quedaría nuestra consulta utilizando la cláusula PIVOT:

SQL> SELECT *
2   FROM (SELECT almacen, producto, precio
3         FROM   productos) prod
4   PIVOT (AVG(precio) FOR producto IN
5   ('ABC123','DEF456','XYZ987'))
6   ORDER BY almacen
7   /

ALMACEN 'ABC123' 'DEF456' 'XYZ987'
------- -------- -------- --------
Ba            95       75      160
Ma           100               150
Va                     80         

Otro artículo del tutorial PL/SQL: La nueva sentencia SQL MERGE.

9 comentarios:

shadowfax dijo...

Interesante la opcion del PIVOT, ¿como podría aplicarlo a una columna con mas 1000 productos y de los cuales no necesariamente conozco la descripcion de todos?

Pepelu dijo...

Hola Shadowfax,

Yo no he probado mucho esta funcionalidad pero yo creo que Oracle precisamente la ha desarrollado para que sea posible hacer lo que propones. Ten en cuenta que necesitas la version de Oracle de base de datos 11g. Yo lo probaría intentando algo como:

SELECT *
FROM (SELECT almacen, producto, precio
FROM productos) prod
PIVOT (AVG(precio) FOR producto IN
(SELECT producto FROM lista_productos WHERE ...))
ORDER BY almacen;

rudy.yupanqui dijo...

Excelente tu blog. Jamas habia escuchado del uso de la clausula PIVOT, pero estoy muy seguro que me sacara de algun apuro de aca en adelante.

Salu2

miguel angel dijo...

Genial buen Aporte :D me hizo recordar al Cubo , claro aunque el un Cubotiene diferente estructura de todas maneras gracias por el aporte

RONALDINHO dijo...

buena utilidad pero cuando uso este select me da error:

select *
from
(
select
trabajador,
concepto,
monto_concepto
from
sisper.pa_planilla_calculada_mes
where
ano = '2012' and mes = '06') es
pivot
(sum(monto_concepto) for concepto in (select concepto from
sisper.pa_planilla_calculada_mes group by concepto))
order by trabajador;

pero el SQL Developer me bota el siguiente error:

ORA-00936: falta una expresión
00936. 00000 - "missing expression"
*Cause:
*Action:
Error en la línea: 13, columna: 38

Juan Felipe Bustamante Duque dijo...

Si claro, es porque en Oracle el IN de la sentencia PIVOT no es dinámica, debes escribir los nombres de las columnas que deseas pivotear.

Tristemente tiene esa limitacion

Anónimo dijo...

Excelente aporte, muy bien explicado, lo hice y funcionó perfecto tanto el pivot como el decode.

Muchas gracias

Lu dijo...

Muchas gracias, me ha servido de ayuda,

Jacqueline Gil Tapia-Ruano dijo...

Excelente aporte, de verlo ya me han dado deseos de aplicarlo :)

Muchas gracias