Este artículo muestra cómo utilizar los nuevos operadores PIVOT y UNPIVOT en 11g, además de dar una solución anterior a 11g a los mismos problemas.
- PIVOT
- UNPIVOT
PIVOT
El operador PIVOT toma datos en filas separadas, los agrega y los convierte en columnas. Para ver al operador PIVOT en acción, necesitamos crear una tabla de prueba.
CREATE TABLE pivot_test (
id NUMBER,
customer_id NUMBER,
product_code VARCHAR2(5),
quantity NUMBER
);
INSERT INTO pivot_test VALUES (1, 1, 'A', 10);
INSERT INTO pivot_test VALUES (2, 1, 'B', 20);
INSERT INTO pivot_test VALUES (3, 1, 'C', 30);
INSERT INTO pivot_test VALUES (4, 2, 'A', 40);
INSERT INTO pivot_test VALUES (5, 2, 'C', 50);
INSERT INTO pivot_test VALUES (6, 3, 'A', 60);
INSERT INTO pivot_test VALUES (7, 3, 'B', 70);
INSERT INTO pivot_test VALUES (8, 3, 'C', 80);
INSERT INTO pivot_test VALUES (9, 3, 'D', 90);
INSERT INTO pivot_test VALUES (10, 4, 'A', 100);
COMMIT;
Entonces, nuestros datos de prueba comienzan con este aspecto.
SELECT * FROM pivot_test;
ID CUSTOMER_ID PRODU QUANTITY
---------- ----------- ----- ----------
1 1 A 10
2 1 B 20
3 1 C 30
4 2 A 40
5 2 C 50
6 3 A 60
7 3 B 70
8 3 C 80
9 3 D 90
10 4 A 100
10 rows selected.
SQL>
En su forma básica, el operador PIVOT es bastante limitado. Nos vemos obligados a listar los valores requeridos para PIVOT usando la cláusula IN.
SELECT *
FROM (SELECT product_code, quantity
FROM pivot_test)
PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c));
A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
-------------- -------------- --------------
210 90 160
1 row selected.
SQL>
Si queremos desglosarlo por cliente, simplemente incluimos la columna CUSTOMER_ID en la lista de selección inicial.
SELECT *
FROM (SELECT customer_id, product_code, quantity
FROM pivot_test)
PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c))
ORDER BY customer_id;
CUSTOMER_ID A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
----------- -------------- -------------- --------------
1 10 20 30
2 40 50
3 60 70 80
4 100
4 rows selected.
SQL>
Antes de 11g podíamos lograr un resultado similar usando la función DECODE combinada con funciones agregadas.
SELECT SUM(DECODE(product_code, 'A', quantity, 0)) AS a_sum_quantity, SUM(DECODE(product_code, 'B', quantity, 0)) AS b_sum_quantity, SUM(DECODE(product_code, 'C', quantity, 0)) AS c_sum_quantity FROM pivot_test ORDER BY customer_id; A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY -------------- -------------- -------------- 210 90 160 1 row selected. SQL> SELECT customer_id, SUM(DECODE(product_code, 'A', quantity, 0)) AS a_sum_quantity, SUM(DECODE(product_code, 'B', quantity, 0)) AS b_sum_quantity, SUM(DECODE(product_code, 'C', quantity, 0)) AS c_sum_quantity FROM pivot_test GROUP BY customer_id ORDER BY customer_id; CUSTOMER_ID A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY ----------- -------------- -------------- -------------- 1 10 20 30 2 40 0 50 3 60 70 80 4 100 0 0 4 rows selected. SQL>
Agregar la palabra clave XML al operador PIVOT nos permite convertir los resultados dinámicos generados a formato XML. También hace que el PIVOT sea un poco más flexible, lo que nos permite reemplazar la cláusula IN codificada de forma rígida con una subconsulta, o el comodín ANY.
SET LONG 10000 SELECT * FROM (SELECT product_code, quantity FROM pivot_test) PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (SELECT DISTINCT product_code FROM pivot_test WHERE id < 10)); product_code_XML --------------------------------------------------------------------------------------------- A210</ item>B90< item>C160 D90 1 row selected. SQL> SELECT * FROM (SELECT product_code, quantity FROM pivot_test) PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (ANY)); product_code_XML ---------------------------------------------------------------------- A210</ item>B90< item>C160 D90 1 row selected. SQL>
Una vez más, los resultados se pueden desglosar por cliente, con el XML de cada cliente presentado como una fila separada.
SET LONG 10000 SELECT * FROM (SELECT customer_id, product_code, quantity FROM pivot_test) PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (SELECT DISTINCT product_code FROM pivot_test)); CUSTOMER_ID ----------- PRODUCT_CODE_XML --------------------------------------------------------------------------------------------- 1 A10</i tem>B20C30D 2 A40</i tem>BC50 D 3 A60</i tem>B70C80D90 CUSTOMER_ID ----------- PRODUCT_CODE_XML ---------------------------------------------------------------------------------------------- 4 A100</ item>BCD 4 rows selected. SQL>
UNPIVOT
El operador UNPIVOT convierte los datos basados en columnas en filas separadas. Para ver al operador UNPIVOT en acción, necesitamos crear una tabla de prueba.
CREATE TABLE unpivot_test (
id NUMBER,
customer_id NUMBER,
product_code_a NUMBER,
product_code_b NUMBER,
product_code_c NUMBER,
product_code_d NUMBER
);
INSERT INTO unpivot_test VALUES (1, 101, 10, 20, 30, NULL);
INSERT INTO unpivot_test VALUES (2, 102, 40, NULL, 50, NULL);
INSERT INTO unpivot_test VALUES (3, 103, 60, 70, 80, 90);
INSERT INTO unpivot_test VALUES (4, 104, 100, NULL, NULL, NULL);
COMMIT;
Entonces, nuestros datos de prueba comienzan con este aspecto.
SELECT * FROM unpivot_test;
ID CUSTOMER_ID PRODUCT_CODE_A PRODUCT_CODE_B PRODUCT_CODE_C PRODUCT_CODE_D
---------- ----------- -------------- -------------- -------------- --------------
1 101 10 20 30
2 102 40 50
3 103 60 70 80 90
4 104 100
4 rows selected.
SQL>
El operador UNPIVOT convierte estos datos basados en columnas en filas individuales.
SELECT * FROM unpivot_test UNPIVOT (quantity FOR product_code IN (product_code_a AS 'A', product_code_b AS 'B', product_code_c AS 'C', product_code_d AS 'D')); ID CUSTOMER_ID P QUANTITY ---------- ----------- - ---------- 1 101 A 10 1 101 B 20 1 101 C 30 2 102 A 40 2 102 C 50 3 103 A 60 3 103 B 70 3 103 C 80 3 103 D 90 4 104 A 100 10 rows selected. SQL>
Hay varias cosas a tener en cuenta sobre la consulta:
Los nombres de columna requeridos, en este caso QUANTITY y PRODUCT_CODE, se definen en la cláusula UNPIVOT. Estos se pueden configurar con cualquier nombre que no esté actualmente en la tabla de conducción.
Las columnas que se van a desvincular se deben nombrar en la cláusula IN.
El valor de PRODUCT_CODE coincidirá con el nombre de la columna de la que se deriva, a menos que le asigne un alias a otro valor.
De forma predeterminada, se utiliza la cláusula EXCLUDE NULLS. Para anular el comportamiento predeterminado, utilice la cláusula INCLUDE NULLS.
La siguiente consulta muestra la inclusión de la cláusula INCLUDE NULLS.
SELECT * FROM unpivot_test UNPIVOT INCLUDE NULLS (quantity FOR product_code IN (product_code_a AS 'A', product_code_b AS 'B', product_code_c AS 'C', product_code_d AS 'D')); ID CUSTOMER_ID P QUANTITY ---------- ----------- - ---------- 1 101 A 10 1 101 B 20 1 101 C 30 1 101 D 2 102 A 40 2 102 B 2 102 C 50 2 102 D 3 103 A 60 3 103 B 70 3 103 C 80 ID CUSTOMER_ID P QUANTITY ---------- ----------- - ---------- 3 103 D 90 4 104 A 100 4 104 B 4 104 C 4 104 D 16 rows selected. SQL>
Antes de 11g, podemos obtener el mismo resultado utilizando la función DECODE y una tabla dinámica con el número correcto de filas. En el siguiente ejemplo usamos la cláusula CONNECT BY en una consulta de dual para generar el número correcto de filas para la operación de desvío.
SELECT id, customer_id, DECODE(unpivot_row, 1, 'A', 2, 'B', 3, 'C', 4, 'D', 'N/A') AS product_code, DECODE(unpivot_row, 1, product_code_a, 2, product_code_b, 3, product_code_c, 4, product_code_d, 'N/A') AS quantity FROM unpivot_test, (SELECT level AS unpivot_row FROM dual CONNECT BY level <= 4) ORDER BY 1,2,3; ID CUSTOMER_ID PRO QUANTITY ---------- ----------- --- ---------- 1 101 A 10 1 101 B 20 1 101 C 30 1 101 D 2 102 A 40 2 102 B 2 102 C 50 2 102 D 3 103 A 60 3 103 B 70 3 103 C 80 3 103 D 90 4 104 A 100 4 104 B 4 104 C 4 104 D 16 rows selected. SQL>
