Pivotado de datos con CASE, crosstab y PIVOT

Qué es pivotar datos

Los datos transaccionales suelen estar en formato largo: cada fila es una observación con columnas (dimension, fecha, valor). Los informes suelen quererlos en formato ancho: una fila por dimensión y una columna por cada valor distinto de otra dimensión.

Formato largo                    Formato pivotado (wide)
categoria | mes  | ventas        categoria   | ene | feb | mar
----------+------+---------      ------------+-----+-----+-----
Ropa      | ene  | 100           Ropa        | 100 | 120 | 150
Ropa      | feb  | 120           Electronica | 300 | 250 | 400
Ropa      | mar  | 150
Electronica|ene  | 300
Electronica|feb  | 250
Electronica|mar  | 400

La transformación larga → ancha es un pivot. La inversa (ancha → larga) es un unpivot. Son operaciones típicas en informes, dashboards y ETL.

Patrón portable: CASE WHEN dentro de agregación

La técnica más universal (funciona en MySQL, PostgreSQL, SQL Server, Oracle, SQLite) es usar SUM o MAX con CASE WHEN por cada columna destino:

SELECT
    categoria,
    SUM(CASE WHEN mes = 'ene' THEN ventas ELSE 0 END) AS ene,
    SUM(CASE WHEN mes = 'feb' THEN ventas ELSE 0 END) AS feb,
    SUM(CASE WHEN mes = 'mar' THEN ventas ELSE 0 END) AS mar,
    SUM(ventas) AS total
FROM ventas_mensuales
WHERE anio = 2026
GROUP BY categoria
ORDER BY categoria;

Por qué SUM y no MAX

SUM(CASE ... END) suma los valores cuando el CASE matchea y 0 cuando no (agregando). MAX(CASE ... END) funciona bien cuando solo hay una fila por combinación (categoria, mes), pero si hay varias, solo coge la mayor.

Para datos con una sola observación por celda (como ventas agregadas ya), ambos valen. Para sumar varias observaciones en la celda pivotada, usa SUM.

FILTER clause (PostgreSQL, SQL Server)

PostgreSQL y otros motores modernos soportan la cláusula FILTER, que es más legible que CASE WHEN:

SELECT
    categoria,
    SUM(ventas) FILTER (WHERE mes = 'ene') AS ene,
    SUM(ventas) FILTER (WHERE mes = 'feb') AS feb,
    SUM(ventas) FILTER (WHERE mes = 'mar') AS mar
FROM ventas_mensuales
WHERE anio = 2026
GROUP BY categoria;

Internamente es equivalente a SUM(CASE WHEN mes='ene' THEN ventas END) (sin ELSE, produce NULL cuando no matchea, que SUM ignora).

PostgreSQL: crosstab del módulo tablefunc

Para pivotados frecuentes, el módulo tablefunc de PostgreSQL incluye la función crosstab() que automatiza el proceso. Primero hay que habilitar la extensión:

CREATE EXTENSION IF NOT EXISTS tablefunc;

Sintaxis básica:

SELECT *
FROM crosstab(
    $$ SELECT categoria, mes, ventas
       FROM ventas_mensuales
       WHERE anio = 2026
       ORDER BY 1, 2 $$
) AS ct(categoria TEXT, ene INT, feb INT, mar INT, abr INT);

La consulta interna debe devolver 3 columnas: (fila, columna_pivot, valor). El AS ct(...) define los nombres y tipos de las columnas del resultado.

Para pivotados con columnas predefinidas (evita problemas si faltan meses en los datos), hay crosstab(source, categories):

SELECT *
FROM crosstab(
    'SELECT categoria, mes, ventas FROM ventas_mensuales WHERE anio=2026 ORDER BY 1',
    'SELECT unnest(ARRAY[''ene'',''feb'',''mar'',''abr''])'
) AS ct(categoria TEXT, ene INT, feb INT, mar INT, abr INT);

SQL Server y Oracle: PIVOT nativo

SQL Server y Oracle tienen la cláusula PIVOT directamente en SQL:

-- SQL Server
SELECT categoria, [ene], [feb], [mar]
FROM (
    SELECT categoria, mes, ventas FROM ventas_mensuales WHERE anio = 2026
) AS src
PIVOT (
    SUM(ventas) FOR mes IN ([ene], [feb], [mar])
) AS pvt;

-- Oracle
SELECT *
FROM (
    SELECT categoria, mes, ventas FROM ventas_mensuales WHERE anio = 2026
)
PIVOT (
    SUM(ventas) FOR mes IN ('ene' AS ene, 'feb' AS feb, 'mar' AS mar)
);

Más limpio, pero no es portable a MySQL ni PostgreSQL (aunque es SQL estándar desde 2008, la adopción es parcial).

UNPIVOT: columnas a filas

El proceso inverso convierte una tabla ancha en larga. Es útil para normalizar datos venidos de Excel u hojas de cálculo.

Patrón portable con UNION ALL

SELECT producto, 'ene' AS mes, ene AS ventas FROM ventas_wide
UNION ALL
SELECT producto, 'feb', feb FROM ventas_wide
UNION ALL
SELECT producto, 'mar', mar FROM ventas_wide
UNION ALL
SELECT producto, 'abr', abr FROM ventas_wide;

PostgreSQL: unnest con arrays

PostgreSQL permite desensamblar con unnest:

SELECT producto, mes, ventas
FROM ventas_wide,
     LATERAL (VALUES
         ('ene', ene), ('feb', feb), ('mar', mar), ('abr', abr)
     ) AS t(mes, ventas)
WHERE ventas IS NOT NULL;

SQL Server: UNPIVOT

SELECT producto, mes, ventas
FROM ventas_wide
UNPIVOT (
    ventas FOR mes IN ([ene], [feb], [mar], [abr])
) AS upvt;

Pivotado dinámico (columnas variables)

Cuando no conoces de antemano qué valores tomará la columna pivote (p. ej. productos que se añadirán), tienes que construir la query dinámicamente en el cliente o con SQL dinámico:

-- PostgreSQL: construir la query como string
DO $$
DECLARE
    q TEXT;
BEGIN
    SELECT 'SELECT categoria, ' ||
           string_agg('SUM(ventas) FILTER (WHERE mes = ''' || mes || ''') AS "' || mes || '"', ', ')
           || ' FROM ventas_mensuales GROUP BY categoria'
    INTO q
    FROM (SELECT DISTINCT mes FROM ventas_mensuales ORDER BY mes) m;

    EXECUTE q;
END $$;

Esta es la única forma de hacer un pivot realmente dinámico en SQL. Es más común hacerlo desde la capa de aplicación (Python/pandas, Node, Java): recuperas los datos en formato largo y los pivotas en código.

Caso real: matriz de correlación de encuestas

Imagina una encuesta con 10 preguntas y 1000 respuestas. Quieres una matriz con preguntas como filas y valores Likert (1-5) como columnas, con el recuento:

SELECT pregunta_id,
       COUNT(*) FILTER (WHERE valor = 1) AS muy_en_desacuerdo,
       COUNT(*) FILTER (WHERE valor = 2) AS en_desacuerdo,
       COUNT(*) FILTER (WHERE valor = 3) AS neutral,
       COUNT(*) FILTER (WHERE valor = 4) AS de_acuerdo,
       COUNT(*) FILTER (WHERE valor = 5) AS muy_de_acuerdo,
       ROUND(AVG(valor)::NUMERIC, 2) AS media,
       COUNT(*) AS respuestas
FROM encuesta_respuestas
WHERE encuesta_id = 42
GROUP BY pregunta_id
ORDER BY pregunta_id;

Este patrón es mucho más rápido que hacer 5 consultas separadas y más legible en el código de reporting que la gestión manual desde pandas.

Buenas prácticas

• Pivota en la base de datos cuando la salida es pequeña (decenas de columnas). Si son cientos, deja el formato largo y pivota en el cliente.
• Usa COALESCE(..., 0) para convertir NULL en 0 en celdas vacías si el reporte lo requiere.
• Documenta qué columnas aparecerán: si es dinámico, el consumidor de los datos (BI, frontend) necesita saberlo.
• Prefiere FILTER sobre CASE WHEN en PostgreSQL por legibilidad.