Datos Pivoting y Unpivoting

El pivoting es un caso especializado de agrupación y agregación de datos. El unpivoting es, en cierto sentido, la inversa del pivoting. T-SQL soporta operadores nativos para ambos. En la primera parte de este post se describe el pivoting y en la segunda parte se describe el unpivoting.

Datos Pivoting

El pivoting es una técnica que agrupa y agrega datos, una transición desde un estado de filas a un estado de columnas. En todas las consultas pivoting, es necesario identificar tres elementos:

·     ¿Qué quiere ver sobre las filas? Este elemento es conocido como sobre filas o el elemento agrupacion.

·        ¿Qué quiere ver sobre las columnas? Este elemento es conocido como sobre columnas, o elemento dispersion.

·     ¿Qué quiere ver en la intersección de cada valor de fila y de columna distinta? Este elemento es conocido como datos, o elemento agregacion.

Como un ejemplo de una solicitud de pivoting, suponga que desea consultar la tabla Sales.Orders. Quiere retornar una fila por cada custid distinto (el elemento de agrupación), una columna por cada shipperid distinto (el elemento de dispersión), y en la intersección de cada customer y shipper, quiere ver la suma de los valores de flete (el elemento de agregación). Con T-SQL, puede lograr una tarea de pivoting utilizando el operador de tabla PIVOT. La forma recomendada para una consulta de pivoting es generalmente como la siguiente.

WITH PivotData AS

(

  SELECT

    <columna agrupacion>,

    <columna dispersion>,

    <columna agregacion>

  FROM <tabla origen>

)

SELECT <lista select>

FROM PivotData

  PIVOT(<funcion agregada>(<columna agregacion>)

    FOR <columna dispersion> IN (<valores dispersion distintos>)) AS P;

Esta forma general recomendada es hecha de los siguientes elementos:

·     Define una expresión de tabla (como la llamada PivotData) que retorna los tres elementos que intervienen en el pivoting. No es recomendado consultar la tabla de origen originaria directamente; la razón de esto se explicará en breve.

·         Emite la consulta externa sobre la expresión de tabla y aplica el operador PIVOT a esa expresión de tabla. El operador PIVOT retorna un resultado de tabla. Necesita asignar un alias a esa tabla, por ejemplo, P.

·      La especificación para el operador PIVOT se inicia indicando una función agregada aplicada al elemento de agregación, en este ejemplo, SUM(freight).

·         Entonces especifica la cláusula FOR seguida por la columna de dispersión, que en este ejemplo es shipperid.

·         Entonces especifica la cláusula IN seguida por la lista de valores distintos que aparecen en el elemento de dispersión, separados por comas. Las que utilizó para ser valores en la columna de dispersión (en este caso, los shipperid) se convierten en nombres de columna en la tabla de resultado. Por lo tanto, los elementos en la lista deberían ser expresados como identificadores de columna. Recuerde que si un identificador de columna es irregular, tiene que ser delimitado. Debido a que los shipperid son enteros, tienen que ser delimitados: [1], [2], [3].

Siguiendo esta sintaxis recomendada para consultas pivoting, la siguiente consulta aborda la tarea de ejemplo (retorna custid sobre las filas, shipperid sobre las columnas, y freight en las intersecciones).

WITH PivotData AS

(

  SELECT

    custid, -- columna agrupacion

    shipperid, -- columna dispersion

    freight -- columna agregacion

  FROM Sales.Orders

)

SELECT custid, [1], [2], [3]

FROM PivotData

  PIVOT(SUM(freight) FOR shipperid IN ([1], [2], [3])) AS P;

Esta consulta genera la siguiente salida (que se muestra aquí en forma abreviada).

custid   1        2        3

------- -------- -------- --------

1        95.03    61.02     69.53

2        43.90    NULL      53.52

3        63.09    116.56    88.87

4        41.95    358.54    71.46

5        189.44   1074.51  295.57

6        0.15     126.19    41.92

7        217.96   215.70    190.00

8        16.16    175.01    NULL

9        341.16   419.57    597.14

10       129.42   162.17    502.36

...

(89 fila(s) afectadas)

Si se fija cuidadosamente en la especificación del operador PIVOT, notará que indica los elementos de agregación y de dispersión, pero no el elemento de agrupación. El elemento de agrupación es identificado por eliminación, es el de la izquierda de la tabla consultada al lado de los elementos de agregación y de dispersión. Es por esto que es recomendado preparar una expresión de tabla, para que el operador pivot retorne sólo los tres elementos que deberían  estar involucrados en la tarea de pivoting. Si se consulta la tabla originaria directamente (Sales.Orders en este caso), todas las columnas de la tabla, además de las columnas de agregación (freight) y de dispersión (shipperid) serán convertidas implícitamente en sus elementos de agrupación. Esto incluye incluso la columna de llave primaria orderid. Así que en vez de conseguir una fila por cliente, se obtiene una fila por pedido. Puede verlo ejecutando el siguiente código.

SELECT custid, [1], [2], [3]

FROM Sales.Orders

  PIVOT(SUM(freight) FOR shipperid IN ([1], [2], [3])) AS P;

Esta consulta genera la siguiente salida (que se muestra aquí en forma abreviada).

custid   1       2        3

------- ------- ------- -------

85       NULL    NULL      32.38

79       11.61   NULL      NULL

34       NULL    65.83     NULL

84       41.34   NULL      NULL

76       NULL    51.30     NULL

34       NULL    58.17     NULL

14       NULL    22.98     NULL

68       NULL    NULL      148.33

88       NULL    13.97     NULL

35       NULL    NULL      81.91

...

(830 fila(s) afectadas)

Obtiene 830 filas de retorno, porque hay 830 filas en la tabla Sales.Orders. Por definir una expresión de tabla como la mostrada en la solución recomendada, controla que columnas serán utilizadas como las columnas de agrupación. Si retorna custid, shipperid y freight en la expresión de tabla, y utiliza los dos últimos como los elementos de dispersión y de agregación, respectivamente, el operador PIVOT implícitamente asume que custid es el elemento de agrupación. Por lo tanto, agrupa los datos por custid, y como resultado, retorna una sola fila por cliente.

Debe ser consciente de algunas limitaciones del operador PIVOT:

·         Los elementos de agregación y de dispersión  no pueden directamente ser resultados de expresiones; sino deben ser los nombres de columna de la tabla consultada. Puede, sin embargo, aplicar expresiones en la consulta definiendo la expresión de tabla, asignando alias a esas expresiones, y luego usar los alias en el operador PIVOT.

·       La función COUNT(*) no es permitida como la función agregada utilizada por el operador PIVOT. Si necesita un conteo, tiene que utilizar la función agregada general COUNT(<nombre col>). Una solución simple es definir una columna ficticia en la expresión de tabla hecha de una constante, como en 1 AS agg_col, y luego en el operador PIVOT aplicar la función agregada a esa columna: COUNT(agg_col).

·         Un operador PIVOT está limitado a utilizar sólo una función agregada.

·       La cláusula IN del operador PIVOT acepta una lista estática de valores de dispersión. No soporta una subconsulta como entrada. Necesita saber con anticipación los valores distintos que están en la columna de dispersión y especificarlos en la cláusula IN. Cuando la lista no es conocida anticipadamente, puede utilizar SQL dinámico para construir y ejecutar la cadena de consulta después de consultar los valores distintos de los datos.

Datos Unpivoting

Los datos unpivoting pueden ser considerados la inversa de los pivoting. El punto de partida son algunos datos pivotados. Cuando unpivotea los datos, rota los datos de entrada de un estado de columnas a un estado de filas. Al igual que T-SQL soporta el operador de tabla PIVOT nativo para realizar el pivoting, soporta un operador UNPIVOT nativo para realizar el unpivoting. Como PIVOT, UNPIVOT son implementados como un operador de tabla que utiliza en la cláusula FROM. El operador opera sobre la tabla de entrada que es proporcionada a su izquierda, lo que podría ser el resultado de otros operadores de tabla, como joins. El resultado del operador UNPIVOT es un resultado de tabla que puede ser utilizado como la entrada a otros operadores de tabla que aparecen a su derecha.

Para demostrar el unpivoting, use como un ejemplo una tabla de muestra llamada Sales.FreightTotals. El siguiente código crea los datos de muestra y lo consulta para mostrar su contenido.

USE TSQL2012;

IF OBJECT_ID('Sales.FreightTotals') IS NOT NULL DROP TABLE Sales.FreightTotals;

GO

WITH PivotData AS

(

  SELECT

    custid,    -- columna agrupacion

    shipperid, -- columna dispersion

    freight    -- columna agregacion

  FROM Sales.Orders

)

SELECT *

INTO Sales.FreightTotals

FROM PivotData

  PIVOT(SUM(freight) FOR shipperid IN ([1], [2], [3])) AS PivotData;

SELECT * FROM Sales.FreightTotals;

Este código genera el siguiente resultado, mostrado aquí en forma abreviada.

custid   1          2       3

------- -------- -------- --------

1        95.03    61.02     69.53

2        43.90    NULL      53.52

3        63.09    116.56    88.87

4        41.95    358.54    71.46

5        189.44   1074.51  295.57

6        0.15     126.19    41.92

7        217.96   215.70    190.00

8        16.16    175.01    NULL

9        341.16   419.57    597.14

10       129.42   162.17    502.36

...

Como puede ver, la tabla de origen tiene una fila por cada cliente y una columna por cada distribuidor (shippers 1, 2 y 3). La intersección de cada cliente y distribuidor tiene los valores del flete total. La tarea unpivoting ayuda a retornar una fila por cada cliente y por cada distribuidor manteniendo el custid en una columna, el shipperid en una segunda columna, y el valor de flete en la tercera columna.

Unpivoting siempre toma un conjunto de columnas de origen y las rota a varias filas, generando dos columnas de destino: uno para mantener los valores de columna de origen y otro para mantener los nombres de columna de origen. Las columnas de origen ya existen, por lo que sus nombres le deben ser conocidos. Pero las dos columnas de destino son creados por la solución unpivoting, por lo que necesita elegirles los nombres. En nuestro ejemplo, las columnas de origen son [1], [2] y [3]. En cuanto a los nombres para las columnas de destino, necesita decidirlos. En este caso, podría ser adecuado llamar a la columna de valores freight y a la columna de nombres shipperid. Así que recuerde, en cada tarea unpivoting, necesita identificar los tres elementos involucrados:

·         El conjunto de columnas de origen que está unpivotando (en este caso, [1], [2], [3])

·         El nombre que desea asignar a la columna de valores de destino (en este caso, freight)

·     El nombre que desea asignar a la columna de nombres de destino (en este caso, shipperid)

Después de identificar estos tres elementos, utilice la siguiente forma de consulta para manejar la tarea unpivoting.

SELECT <lista columna>, <nombres columna>, <valores columna>

FROM <tabla origen>

  UNPIVOT(<valores columna> FOR <nombres columna> IN(<columnas origen>)) AS U;

Basada en esta sintaxis, la siguiente consulta se dirige a la tarea actual.

SELECT custid, shipperid, freight

FROM Sales.FreightTotals

  UNPIVOT(freight FOR shipperid IN([1], [2], [3])) AS U;

Esta consulta genera la siguiente salida (mostrada aquí en forma abreviada).

custid   shipperid freight

------- ---------- --------

1        1          95.03

1        2          61.02

1        3          69.53

2        1          43.90

2        3          53.52

3        1          63.09

3        2          116.56

3        3          88.87

4        1          41.95

4        2          358.54

4        3          71.46

...

Además del unpivoteo de datos, el operador UNPIVOT filtra las filas con NULLs en la columna de valor (freight en este caso). El supuesto es que representan casos inaplicables. No se puede evitar mantener NULLs en el origen si la columna fue aplicable  al menos a otro cliente. Pero después de unpivotar los datos, no hay razón para mantener una fila para un cierto par cliente-distribuidor si es inaplicable, si ese distribuidor no envió pedidos a ese cliente.

En términos de los tipos de datos, la columna de nombres es definido como una cadena de caracteres Unicode (NVARCHAR(128)). La columna de valores es definido con el mismo tipo como el tipo de las columnas de origen que fueron unpivotadas. Por esta razón, los tipos de todas las columnas que está unpivotando deben ser los mismos.

Cuando haya terminado, ejecute el siguiente código para la limpieza.

IF OBJECT_ID('Sales.FreightTotals') IS NOT NULL DROP TABLE Sales.FreightTotals;

Ejercicio 1: Pivotando Datos utilizando una Expresión de Tabla

En este ejercicio, pivota los datos utilizando una expresión de tabla.

1.    Abra el SSMS y conéctese a la base de datos de muestra TSQL2012.

2.    Escriba una consulta PIVOT sobre la tabla Sales.Orders que retorna la fecha de envío máxima por cada año de pedido y shipperid. Retorna años de pedido en filas, shipperid (1, 2, y 3) sobre las columnas, y las fechas de envío máximas en la parte de datos.

En primer lugar, intenta dirigir la tarea utilizando la siguiente consulta.

SELECT YEAR(orderdate) AS orderyear, [1], [2], [3]

FROM Sales.Orders

  PIVOT(MAX(shippeddate) FOR shipperid IN ([1], [2], [3])) AS P;

Espera obtener tres filas en el resultado para los años 2006, 2007 y 2008, pero en su lugar obtiene 830 filas en el resultado, como el número de pedidos en la tabla.

3.    Trate de explicar por qué obtuvo el resultado indeseado y encuentre una solución.

La razón por que obtuvo el resultado indeseado es que consultó la tabla Sales.Orders directamente. La forma en que SQL Server determinó por qué columnas agrupar es por utilizar la eliminación; las columnas de agrupación son todas las columnas que no especifica como de dispersión (shipperid, en este caso) y de agregación (shippeddate, en este caso). Todas las columnas restantes, incluyendo orderid, se convierten implícitamente en parte del grupo por listar. Por lo tanto, obtiene una fila por pedido en lugar de una fila por año. Para corregir el problema, defina una expresión de tabla que contiene sólo las columnas de agrupación, de dispersión, y de agregación, y proporciona la expresión de tabla como entrada a la consulta PIVOT. Su solución debe ser similar a la siguiente.

WITH PivotData AS

(

  SELECT YEAR(orderdate) AS orderyear, shipperid, shippeddate 

  FROM Sales.Orders

)

SELECT orderyear, [1], [2], [3]

FROM PivotData

  PIVOT(MAX(shippeddate) FOR shipperid IN ([1], [2], [3])) AS P;

Aquí está la salida con fechas formateadas y abreviadas.

orderyear    1           2           3

----------- ----------- ----------- -----------

2007         2008-01-30  2008-01-21  2008-01-09

2008         2008-05-04  2008-05-06  2008-05-01

2006         2007-01-03  2006-12-30  2007-01-16

Ejercicio 2: Pivotar Datos y Cálculo de Conteo

En este ejercicio aplicará el agregado COUNT cuando pivota datos. Al igual que en el ejercicio 1, trabaja con la tabla Sales.Orders en la base de datos de muestra TSQL2012.

1.    Escriba una consulta PIVOT que retorna una fila por cada custid distinto, una columna por cada shipperid distinto, y el conteo de pedidos en las intersecciones customer-shipper. Preparar una expresión de tabla que retorne sólo las columnas custid y shipperid de la tabla Sales.Orders, y proporcionar esta expresión de tabla como entrada para el operador PIVOT.

Como un primer intento, trate de utilizar la función agregada COUNT(*), como sigue.

WITH PivotData AS

(

  SELECT

    custid, -- columna agrupacion

    shipperid – columns dispersion 

  FROM Sales.Orders

)

SELECT custid, [1], [2], [3]

FROM PivotData

  PIVOT(COUNT(*) FOR shipperid IN ([1], [2], [3])) AS P;

Debido a que el operador PIVOT no soporta la función agregada COUNT(*), obtiene el siguiente error.

Mens. 102, Nivel 15, Estado 1, Línea 10

Sintaxis incorrecta cerca de '*'.

2.    Trate de pensar en una solución a este problema.

Para resolver el problema, necesita utilizar la función de conjunto general COUNT(<nombre_col>), pero recuerde que la entrada a la función agregada no puede ser el resultado de una expresión; en cambio, debe ser un nombre de columna que exista en la tabla consultada. Así que una opción que tiene, es utilizar la columna de dispersión como la columna de agregación, como en COUNT(shipperid). La otra opción es crear una columna ficticia de una expresión constante en la expresión de tabla, y luego usar esa columna como entrada a la función COUNT, como sigue.

WITH PivotData AS

(

  SELECT

    custid,     -- columna agrupacion

    shipperid,  -- columna dispersion 

    1 AS aggcol -- columna agregacion

  FROM Sales.Orders

)

SELECT custid, [1], [2], [3]

FROM PivotData

  PIVOT(COUNT(aggcol) FOR shipperid IN ([1], [2], [3])) AS P;

Esta consulta genera la salida deseada.

custid   1  2    3

------- --- --- ---

1      4   1   1

2      1   0   3

3      2   3   2

4      1   8   4

5      5   9   4

6      1   3   3

7      5   3   3

8      1   2   0

9      6   7   4

10      3   3   8

...