Lección 12.9 · Tiempo de lectura: ~7 min

Un pivot convierte los valores de una columna en columnas separadas: en lugar de filas "categoría, clasificación, cantidad", obtienes una fila por categoría con una columna para cada clasificación, como en una tabla dinámica de hoja de cálculo. Los pivots son la base de la mayoría de los informes cruzados, y "filas a columnas" es un ejercicio común de entrevista. En esta lección harás un pivot con agregación condicional, que funciona en cualquier base de datos, construirás un pivot dinámico en MySQL, verás el operador PIVOT de SQL Server y volverás a convertir columnas en filas.

Pivot en SQL: filas a columnas

GROUP BY category, rating da los números correctos en una forma incómoda: 80 filas de pares categoría–clasificación. Una matriz se lee más fácil: las categorías hacia abajo y las clasificaciones de lado. MySQL, MariaDB, PostgreSQL y SQLite no tienen la palabra clave PIVOT, y no la necesitan: cada columna de salida es una agregación con una condición. Es la agregación condicional de la lección 4.4, aplicada para cambiar la forma del resultado.

Los ejemplos se ejecutan en la base de datos de ejemplo Sakila (MySQL 8.0).

Pivot con agregación condicional

Agrupa por la etiqueta de las filas (la categoría) y, para cada columna futura, cuenta solo las filas de esa clasificación:

SELECT
    c.name AS category,
    SUM(CASE WHEN f.rating = 'G' THEN 1 ELSE 0 END) AS g,
    SUM(CASE WHEN f.rating = 'PG' THEN 1 ELSE 0 END) AS pg,
    SUM(CASE WHEN f.rating = 'PG-13' THEN 1 ELSE 0 END) AS pg_13,
    SUM(CASE WHEN f.rating = 'R' THEN 1 ELSE 0 END) AS r,
    SUM(CASE WHEN f.rating = 'NC-17' THEN 1 ELSE 0 END) AS nc_17,
    COUNT(*) AS total
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
GROUP BY c.name
ORDER BY c.name;

Resultado: 16 filas, una por categoría, con la cantidad de películas de cada clasificación y el total.

Cómo funciona:

• GROUP BY c.name define las filas del pivot.
• Cada CASE convierte una película en 1 si pertenece a esa columna y en 0 si no, así que SUM cuenta las películas de una clasificación.
• Para hacer pivot de importes en lugar de conteos, pon el valor en el CASE: SUM(CASE WHEN ... THEN amount ELSE 0 END).

En MySQL, una comparación devuelve 1 o 0, así que SUM(f.rating = 'G') es un equivalente más corto. PostgreSQL ofrece COUNT(*) FILTER (WHERE f.rating = 'G').

Pivot por mes

Un informe de negocio frecuente: ingresos por tienda, con una columna por mes. Los valores son importes y la condición es el mes:

SELECT
    s.store_id,
    SUM(CASE WHEN MONTH(p.payment_date) = 5 THEN p.amount ELSE 0 END) AS may_2005,
    SUM(CASE WHEN MONTH(p.payment_date) = 6 THEN p.amount ELSE 0 END) AS jun_2005,
    SUM(CASE WHEN MONTH(p.payment_date) = 7 THEN p.amount ELSE 0 END) AS jul_2005,
    SUM(CASE WHEN MONTH(p.payment_date) = 8 THEN p.amount ELSE 0 END) AS aug_2005
FROM payment p
JOIN staff s ON s.staff_id = p.staff_id
WHERE p.payment_date >= '2005-01-01'
  AND p.payment_date < '2006-01-01'
GROUP BY s.store_id
ORDER BY s.store_id;

Resultado: 2 filas; por ejemplo, la tienda 1 ganó 2621.83 en mayo y 14003.54 en julio de 2005.

La condición WHERE limita los datos a un año, porque MONTH() por sí solo mezclaría julio de 2005 con julio de 2006. Usa ELSE 0 cuando una celda vacía deba mostrar 0; sin ELSE, SUM devuelve NULL para ella.

Pivot dinámico en MySQL

Todas las columnas de una consulta deben conocerse antes de ejecutarla. Si la lista de valores del pivot cambia (nuevas clasificaciones, nuevos meses), hay que generar la consulta. En MySQL, arma la lista de columnas con GROUP_CONCAT y ejecuta el texto como una sentencia preparada:

SET @columns = (
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT('SUM(f.rating = ''', rating, ''') AS `', rating, '`')
        ORDER BY rating)
    FROM film
);

SET @sql = CONCAT(
    'SELECT c.name AS category, ', @columns, '
     FROM film f
     JOIN film_category fc ON fc.film_id = f.film_id
     JOIN category c ON c.category_id = fc.category_id
     GROUP BY c.name
     ORDER BY c.name');

PREPARE pivot_stmt FROM @sql;
EXECUTE pivot_stmt;
DEALLOCATE PREPARE pivot_stmt;

Resultado: las mismas 16 categorías con una columna por clasificación; las columnas se construyen a partir de los datos.

Dos advertencias: los valores se insertan en el texto SQL, así que usa esto solo con valores de tus propias tablas, nunca con datos que escriba el usuario; y las listas de columnas muy largas pueden necesitar un group_concat_max_len mayor (1024 caracteres por defecto). Muchas veces es más simple devolver el resultado largo del GROUP BY y hacer el pivot en la aplicación o en la herramienta de BI.

El operador PIVOT en SQL Server y Oracle

SQL Server y Oracle tienen una sintaxis propia. El mismo informe en SQL Server:

SELECT category, [G], [PG], [PG-13], [R], [NC-17]
FROM (
    SELECT c.name AS category, f.rating, f.film_id
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    JOIN category c ON c.category_id = fc.category_id
) AS src
PIVOT (
    COUNT(film_id) FOR rating IN ([G], [PG], [PG-13], [R], [NC-17])
) AS pvt;

Los valores de IN (...) igual hay que enumerarlos de antemano. La agregación condicional hace lo mismo y funciona en todas partes, así que es la respuesta más segura en una entrevista.

Unpivot: columnas de vuelta a filas

La tarea inversa aparece cuando los datos llegan como una hoja de cálculo: una columna por mes. Haz el unpivot con un SELECT por columna, unidos con UNION ALL:

WITH store_months AS (
    SELECT
        s.store_id,
        SUM(CASE WHEN MONTH(p.payment_date) = 7 THEN p.amount ELSE 0 END) AS jul_2005,
        SUM(CASE WHEN MONTH(p.payment_date) = 8 THEN p.amount ELSE 0 END) AS aug_2005
    FROM payment p
    JOIN staff s ON s.staff_id = p.staff_id
    WHERE p.payment_date >= '2005-07-01'
      AND p.payment_date < '2005-09-01'
    GROUP BY s.store_id
)
SELECT store_id, '2005-07' AS month, jul_2005 AS amount FROM store_months
UNION ALL
SELECT store_id, '2005-08', aug_2005 FROM store_months
ORDER BY store_id, month;

Resultado: 4 filas, una por tienda y mes, por ejemplo tienda 1 – 2005-07 – 14003.54.

SQL Server y Oracle también tienen el operador UNPIVOT; PostgreSQL puede usar CROSS JOIN LATERAL (VALUES ...).

Práctica

• Distribución de la población (pivot)
• Distribución de películas por categoría y tienda
• Ingresos por mes y tipo de pago

Conclusiones clave:

• Un pivot convierte los valores de una columna en columnas separadas.
• GROUP BY define las filas; SUM(CASE WHEN column = value THEN ... ELSE 0 END) crea cada columna. Funciona en cualquier base de datos.
• Para importes, pon el valor en THEN; para conteos, pon 1.
• Cuando no se conocen las columnas de antemano, genera la consulta: en MySQL con GROUP_CONCAT y PREPARE.
• SQL Server y Oracle tienen PIVOT y UNPIVOT; UNION ALL hace el unpivot en cualquier lado.

Preguntas frecuentes

¿Cómo convierto filas en columnas en MySQL?

MySQL no tiene el operador PIVOT. Usa agregación condicional: GROUP BY por la etiqueta de las filas y un SUM(CASE WHEN pivot_column = 'value' THEN amount ELSE 0 END) AS value por cada columna de salida.

¿Cómo creo un pivot con columnas dinámicas?

Genera el texto SQL: reúne los valores distintos con GROUP_CONCAT, arma con ellos la lista de SUM(CASE ...) y luego ejecuta la sentencia con PREPARE y EXECUTE. En SQL Server se hace lo mismo con STRING_AGG y sp_executesql. Usa solo valores confiables de tus propias tablas.

¿Cuál es la diferencia entre PIVOT y la agregación condicional?

Dan el mismo resultado. PIVOT es una sintaxis más corta, disponible solo en SQL Server y Oracle. La agregación condicional funciona en cualquier base de datos y permite condiciones y agregaciones distintas para cada columna.

¿Cómo hago el unpivot de columnas a filas?

Escribe un SELECT por columna, cada uno devolviendo la clave, el nombre de la columna como literal y el valor de la columna, y combínalos con UNION ALL. SQL Server y Oracle también tienen UNPIVOT.

→ Más ejercicios con la base de datos Sakila