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:
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?
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;
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
Genial buen Aporte :D me hizo recordar al Cubo , claro aunque el un Cubotiene diferente estructura de todas maneras gracias por el aporte
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
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
Excelente aporte, muy bien explicado, lo hice y funcionó perfecto tanto el pivot como el decode.
Muchas gracias
Muchas gracias, me ha servido de ayuda,
Excelente aporte, de verlo ya me han dado deseos de aplicarlo :)
Muchas gracias
Publicar un comentario