UNPIVOT

 Ahora vamos a ver el nuevo operador UNPIVOT. Como su nombre indica, una operación UNPIVOT es lo contrario de pivote (aunque sin la posibilidad de desagregar los datos). Una manera más simple de pensar en UNPIVOT es que convierte columnas pivota en filas (una fila de datos para cada columna sea no girados). Veremos ejemplos de esto más adelante, pero Comenzaremos con una descripción general de la sintaxis, de la siguiente manera.

 La sintaxis es similar a la de pivote con algunas ligeras diferencias, incluyendo el significado de las diferentes cláusulas. Estos se describen como sigue:

unpivot_clause: esta cláusula especifica un nombre para una columna para representar los valores de medida no girados. En nuestros ejemplos anteriores de pivote, la columna de la medida fue la suma de los salarios de cada puesto de trabajo y la agrupación departamento;

unpivot_for_clause: la unpivot_for_clause especifica el nombre de la columna que será el resultado de nuestra búsqueda UNPIVOT. Los datos en esta columna describe los valores del indicador en la columna unpivot_clause; y

unpivot_in_clause: este contiene la lista de columnas giradas (no valores) que no girados.

Las cláusulas UNPIVOT son muy difíciles de describir y se sirve mejor a algunos ejemplos.

 UNPIVOT ejemplos simples

Antes de escribir una consulta UNPIVOT, vamos a crear un conjunto de datos pivota para utilizar en nuestros ejemplos. Para simplificar, vamos a crear una vista usando una de nuestras consultas de pivote anteriores, de la siguiente manera.

SQL> CREATE VIEW pivoted_data
  2  AS
  3     SELECT *
  4     FROM   pivot_data
  5     PIVOT (SUM(sal)
  6     FOR    deptno IN (10 AS d10_sal,
  7                       20 AS d20_sal,
  8                       30 AS d30_sal,
  9                       40 AS d40_sal));

Ver creado.
La vista PIVOTED_DATA contiene nuestra norma suma de los sueldos por departamento de trabajo, con los cuatro almacenes totales pivotantes como hemos visto a lo largo de este artículo. Como recordatorio final de la naturaleza de los datos, vamos a consultar este punto de vista.

SQL> SELECT *
  2  FROM   pivoted_data;

JOB           D10_SAL    D20_SAL    D30_SAL    D40_SAL
---------- ---------- ---------- ---------- ----------
CLERK            1430       2090       1045
SALESMAN                               6160
PRESIDENT        5500
MANAGER          2695     3272.5       3135
ANALYST                     6600

5 rows selected.

Ahora vamos a UNPIVOT nuestro conjunto de datos utilizando la nueva sintaxis 11g de la siguiente manera.

SQL> SELECT *
  2  FROM   pivoted_data
  3  UNPIVOT (
  4               deptsal                              --<-- unpivot_clause
  5           FOR saldesc                              --<-- unpivot_for_clause
  6           IN  (d10_sal, d20_sal, d30_sal, d40_sal) --<-- unpivot_in_clause
  7          );

JOB        SALDESC       DEPTSAL
---------- ---------- ----------
CLERK      D10_SAL          1430
CLERK      D20_SAL          2090
CLERK      D30_SAL          1045
SALESMAN   D30_SAL          6160
PRESIDENT  D10_SAL          5500
MANAGER    D10_SAL          2695
MANAGER    D20_SAL        3272.5
MANAGER    D30_SAL          3135
ANALYST    D20_SAL          6600

9 rows selected.

Podemos ver en los resultados que Oracle ha incorporado a cada una de nuestras columnas articuladas en el unpivot_in_clause y los convirtió en filas de datos que describen nuestra medida (es decir, 'D10_SAL', 'D20_SAL' y así sucesivamente). El unpivot_for_clause da esta nueva columna no girados un nombre (es decir, "SALDESC"). El unpivot_clause sí define nuestros datos de la medida, que en este caso es la suma del sueldo del departamento de trabajo.

Es importante tener en cuenta que las consultas UNPIVOT pueden trabajar en cualquier columna (es decir, no sólo los agregados o abatible, columnas). Estamos utilizando el conjunto de datos pivota para mantener la coherencia, pero que podría fácilmente UNPIVOT las columnas de cualquier tabla o vista que tenemos.

Manejo de datos nulos

El número máximo de filas que se pueden devolver por una consulta UNPIVOT es el número de agrupaciones distintas, multiplicado por el número de columnas pivote (en nuestros ejemplos, 5 (puestos de trabajo) * 4 columnas (pivote) = 20). Sin embargo, nuestra primera consulta UNPIVOT sólo ha regresado nueve filas. Si nos fijamos en los datos de pivote fuente misma, podemos ver nueve valores no nulos en las columnas pivote; en otras palabras, once agrupaciones son nulos. El comportamiento predeterminado de UNPIVOT es excluir valores nulos, pero sí tenemos una opción para incluirlos, de la siguiente manera.

 SQL> SELECT *
  2  FROM   pivoted_data
  3  UNPIVOT INCLUDE NULLS
  4        (deptsal
  5  FOR    saldesc IN (d10_sal,
  6                     d20_sal,
  7                     d30_sal,
  8                     d40_sal));

JOB        SALDESC       DEPTSAL
---------- ---------- ----------
CLERK      D10_SAL          1430
CLERK      D20_SAL          2090
CLERK      D30_SAL          1045
CLERK      D40_SAL
SALESMAN   D10_SAL
SALESMAN   D20_SAL
SALESMAN   D30_SAL          6160
SALESMAN   D40_SAL
PRESIDENT  D10_SAL          5500
PRESIDENT  D20_SAL
PRESIDENT  D30_SAL
PRESIDENT  D40_SAL
MANAGER    D10_SAL          2695
MANAGER    D20_SAL        3272.5
MANAGER    D30_SAL          3135
MANAGER    D40_SAL
ANALYST    D10_SAL
ANALYST    D20_SAL          6600
ANALYST    D30_SAL
ANALYST    D40_SAL

20 rows selected.

Con la inclusión de los valores nulos de pivote, volvemos el número máximo de filas posibles de nuestro conjunto de datos. Por supuesto, ahora tenemos once valores nulos, pero esto podría ser algo que se requiere para generar informes o "densificación de datos".