PIVOT y UNPIVOT en SQL Server

Una consulta agrupada devuelve los datos “hacia abajo”: una fila por cliente y mes, por ejemplo. Pero un informe suele pedirlos “hacia el lado”: una fila por cliente y una columna por mes, como una tabla dinámica de Excel. Esa transformación de filas en columnas se llama pivotar.

SQL Server tiene el operador PIVOT para hacerlo. Además existe una alternativa estándar, con CASE dentro de una función agregada, que funciona en cualquier base de datos y que a menudo es más fácil de leer.

El punto de partida

Las ventas agrupadas por cliente y mes, en formato vertical:

Queremos lo mismo con una columna para junio, otra para julio y otra para septiembre.

Con PIVOT (SQL Server)

SELECT columnasFijas, [valor1], [valor2], ...
FROM (consultaOrigen) AS origen
PIVOT (
    funcionAgregada(columnaAAgregar)
    FOR columnaQueSeConvierteEnColumnas IN ([valor1], [valor2], ...)
) AS tablaPivotada;

Aplicado a nuestro ejemplo:

SELECT nombre, [6] AS junio, [7] AS julio, [9] AS septiembre
FROM (
    SELECT C.nombre,
           MONTH(P.fechaPedido) AS mes,
           P.totalPrecio
    FROM Pedidos P
    INNER JOIN Clientes C ON C.idClientes = P.idClientes
    WHERE P.fechaPedido >= '2022-06-01'
) AS origen
PIVOT (
    SUM(totalPrecio)
    FOR mes IN ([6], [7], [9])
) AS ventasPorMes
ORDER BY nombre;

Tres detalles de PIVOT:

• Los valores del IN hay que escribirlos a mano. Si aparece un mes nuevo, la consulta no lo mostrará hasta que lo añadas (o generes la consulta con SQL dinámico).
• Todas las columnas de la consulta de origen que no son ni la agregada ni la del FOR se usan para agrupar. Por eso conviene que el origen tenga solo las columnas necesarias.
• Las celdas sin datos salen como NULL.

Sin PIVOT: CASE dentro de SUM

La misma tabla con SQL estándar. Cada columna es una suma que solo cuenta las filas de su mes. Esta sí puedes ejecutarla:

El resultado es idéntico al de PIVOT. Ventajas de esta forma:

• Funciona en SQL Server, PostgreSQL, MySQL, Oracle y SQLite.
• Cada columna puede tener su propia condición o su propia función: una columna con la suma de julio, otra con el número de pedidos de septiembre, otra con el máximo del año…
• Si añades ELSE 0, las celdas vacías salen como 0 en lugar de NULL.

Otro ejemplo: unidades por familia de producto

Pivotar no tiene por qué ser por fechas. Unidades vendidas a cada cliente, separadas en consolas y el resto:

UNPIVOT: de columnas a filas

UNPIVOT hace el camino inverso: convierte columnas en filas. Es útil cuando recibes datos en formato de hoja de cálculo (una columna por mes) y necesitas tenerlos en formato de tabla para poder agruparlos o unirlos.

SELECT nombre, mes, ventas
FROM ventasPorMes
UNPIVOT (
    ventas FOR mes IN (junio, julio, septiembre)
) AS filas;

La alternativa estándar es un UNION ALL con una consulta por columna:

SELECT nombre, 'junio' AS mes, junio AS ventas FROM ventasPorMes WHERE junio IS NOT NULL
UNION ALL
SELECT nombre, 'julio', julio FROM ventasPorMes WHERE julio IS NOT NULL
UNION ALL
SELECT nombre, 'septiembre', septiembre FROM ventasPorMes WHERE septiembre IS NOT NULL;

Practica

1. Añade a la consulta con CASE una columna con el total de cada cliente.
2. Pivota el número de pedidos (no el importe) por mes.
3. Haz que las celdas vacías muestren 0 en lugar de NULL.