PIVOT

La cláusula PIVOT Oracle le permite escribir una consulta de partida tabulación cruzada en Oracle 11g. Esto significa que usted puede agregar sus resultados y rotar filas en columnas.

Sintaxis

La sintaxis de la cláusula PIVOT en Oracle / PLSQL es:

SELECT * FROM
(
  SELECT column1, column2
  FROM tables
  WHERE conditions
)
PIVOT 
(
  aggregate_function(column2)
  FOR column2
  IN ( expr1, expr2, ... expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];

Parámetros o argumentos

aggregate_function

Puede ser una función tal como SUM , COUNT , MIN , MAX o AVG funciones.

EN (expr1, expr2, ... expr_n)

Una lista de valores para columna2 pivote en capítulos de los resultados de la consulta de tabulación cruzada.

subconsulta

Se puede utilizar en lugar de una lista de valores. En este caso, los resultados de la subconsulta se utilizan para determinar los valores para column2 para pivotar en partidas de los resultados de la consulta de tabulación cruzada.

Se aplica a

La cláusula PIVOT se puede utilizar en las siguientes versiones de Oracle / PLSQL:

• Oracle 12c, 11g de Oracle

Ejemplo

Veamos cómo utilizar la cláusula PIVOT en Oracle.

Vamos a basar nuestro ejemplo sobre una mesa de llamadas órdenes con la siguiente definición:

CREATE TABLE orders
( order_id integer NOT NULL,
  customer_ref varchar2(50) NOT NULL,
  order_date date,
  product_id integer,
  quantity integer,
  CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

Para mostrar los datos de este ejemplo, vamos a seleccionar los registros de la órdenes tabla con la siguiente instrucción SELECT:

SELECT order_id, customer_ref, product_id
FROM orders
ORDER BY order_id;

Estos son los registros en el orden tabla. Vamos a utilizar estos registros para demostrar cómo funciona la cláusula PIVOT:

Solicitar ID	customer_ref	ID del Producto
50001	HERRERO	10
50002	HERRERO	20
50003	ANDERSON	30
50004	ANDERSON	40
50005	JONES	10
50006	JONES	20
50007	HERRERO	20
50008	HERRERO	10
50009	HERRERO	20

Ahora, vamos a crear nuestra consulta tabulación cruzada utilizando la siguiente cláusula PIVOT:

SELECT * FROM
(
  SELECT customer_ref, product_id
  FROM orders
)
PIVOT
(
  COUNT(product_id)
  FOR product_id IN (10, 20, 30)
)
ORDER BY customer_ref;

En este ejemplo, la cláusula PIVOT devolvería los siguientes resultados:

customer_ref	10	20	30
ANDERSON	0	0	1
JONES	1	1	0
HERRERO	2	3	0

Ahora, vamos a romper aparte la cláusula PIVOT y explicar cómo funcionaba.

Especificar campos que desea incluir

En primer lugar, queremos especificar qué campos a incluir en nuestra tabulación cruzada. En este ejemplo, queremos incluir la customer_ref y product_id campos. Esto se hace por la parte siguiente de la declaración:

(
  SELECT customer_ref, product_id
  FROM orders
)

Se pueden listar las columnas que se incluirán en cualquier orden.

Especificar función de agregado

A continuación, tenemos que especificar qué función agregada de usar al crear nuestra consulta tabulación cruzada. Se puede utilizar cualquier agregado tales como SUM , COUNT , MIN , MAX o AVG funciones.

En este ejemplo, vamos a utilizar la función COUNT. Esto contará el número de product_id valores que cumplan con nuestros criterios. Esto se hace por la parte siguiente de la declaración:

PIVOT
(
  COUNT(product_id)

Especificar valores de pivote

Por último, tenemos que especificar qué valores de pivote para incluir en nuestros resultados. Estos serán utilizados como los encabezados de columna en nuestra consulta de tabulación cruzada. Puede utilizar cualquiera de una lista de valores entre paréntesis o una sub consulta para especificar los valores de pivote.

En este ejemplo, vamos a devolver sólo los siguientes valores product_id: 10, 20, 30. Estos valores se convertirán en nuestros títulos de las columnas en nuestra consulta de tabulación cruzada. Además, tenga en cuenta que estos valores son una lista finita de los product_id valores y no necesariamente contener todos los valores posibles.

Esto se hace por la parte siguiente de la declaración:

  FOR product_id IN (10, 20, 30)
)

Ahora bien, cuando lo ponemos todos juntos, obtenemos la siguiente tabla dinámica:

customer_ref	10	20	30
ANDERSON	0	0	1
JONES	1	1	0
HERRERO	2	3	0

Excelente contenido, gracias a la gente de:

techonthenet.com