Oracle/PLSQL: cláusula PIVOT

Los datos expuestos en este blog, son solo de índole informativo. Por favor realiza siempre una copia de seguridad antes de realizar cualquier cambio en tu proyecto.

Oracle/PLSQL: cláusula PIVOT

Este tutorial de Oracle describe cómo usar Oracle cláusula PIVOTE Con sintaxis y ejemplos.

describir

La cláusula PIVOT de Oracle le permite escribir consultas cruzadas a partir de Oracle 11g. Esto significa que puede agregar los resultados y rotar las 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ámetro o parámetro

La función agregada puede ser una función como SUM, COUNT, MIN, MAX o AVG. IN ( expr1, expr2, … expr_n ) Una lista de valores para column2 que se transpondrán a los encabezados en los resultados de la consulta de tabla cruzada. La subconsulta se puede utilizar en lugar de una lista de valores. En este caso, los resultados de la subconsulta se usarán para determinar el valor de la columna 2 para ir a los encabezados en los resultados de la consulta de tabla cruzada.

aplicar para

La cláusula PIVOT está disponible en las siguientes versiones de Oracle/PLSQL:

• Oráculo 12c, Oráculo 11g

ejemplo

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

Nuestro ejemplo se basará en una tabla llamada pedidos, que se define de la siguiente manera:

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 mostrarle los datos de este ejemplo, usaremos la siguiente instrucción SELECT para seleccionar registros de la tabla de pedidos:

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

Estos son los registros en la tabla de pedidos. Usaremos estos registros para demostrar cómo funciona la cláusula PIVOT:

Solicitar ID	Referencia de cliente	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, creemos una consulta de tabla cruzada con 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:

Referencia de cliente	10	20	30
anderson	0	0	1
jones	1	1	0
Herrero	2	3	0

Ahora, analicemos la cláusula PIVOT y expliquemos cómo funciona.

Especifique los campos a incluir

Primero, queremos especificar los campos que se incluirán en la tabla de referencias cruzadas. En este ejemplo, queremos incluir los campos customer_ref y product_id. Esto se hace mediante la siguiente parte de la declaración:

(
  SELECT customer_ref, product_id
  FROM orders
)

Puede enumerar las columnas para incluir en cualquier orden.

especificar función agregada

A continuación, debemos especificar la función agregada que se usará al crear la consulta de tabla cruzada. Puede utilizar cualquier agregado, como las funciones SUM, COUNT, MIN, MAX o AVG.

En este ejemplo, usaremos la función CONTAR. Esto contará la cantidad de valores de product_id que cumplen con nuestros criterios. Esto se hace mediante la siguiente parte de la declaración:

PIVOT
(
  COUNT(product_id)

Especificar el valor de pivote

Finalmente, necesitamos especificar el valor de pivote para incluir en el resultado. Estos se utilizarán como encabezados de columna en nuestra consulta de tabla cruzada. Puede especificar valores pivote utilizando una lista de valores entre paréntesis o una subconsulta.

En este ejemplo, solo devolveremos los siguientes valores de product_id: 10, 20, 30. Estos valores serán los encabezados de columna en la consulta de tabla cruzada. Además, tenga en cuenta que estos valores son una lista limitada de valores product_id, no necesariamente todos los valores posibles.

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

  FOR product_id IN (10, 20, 30)
)

Ahora, cuando los juntamos, obtenemos la siguiente tabla dinámica:

Referencia de cliente	10	20	30
anderson	0	0	1
jones	1	1	0
Herrero	2	3	0