Convertir Filas a Columnas en SQL: Una Guía Completa

¿Qué significa convertir filas en columnas en SQL?

El pivote en SQL se refiere a transformar datos de un formato basado en filas a un formato basado en columnas. Esta transformación es útil para informes y análisis de datos, permitiendo una vista de datos más estructurada y compacta. Pivoteando filas a columnas también permite a los usuarios analizar y resumir datos de una manera que resalta claramente ideas clave.

Considera el siguiente ejemplo: Tengo una tabla con transacciones de ventas diarias, y cada fila registra la fecha, el nombre del producto y la cantidad de ventas.

Al pivotar esta tabla, puedo reestructurarla para mostrar cada producto como una columna, con datos de ventas para cada fecha bajo su columna correspondiente. También se observa que se realiza una agregación.

Tradicionalmente, las operaciones de pivote requerían consultas SQL complejas con agregación condicional. Con el tiempo, las implementaciones de SQL han evolucionado, y muchos de los sistemas de bases de datos modernos ahora incluyen operadores PIVOT y UNPIVOT para permitir transformaciones más eficientes y directas.

Comprendiendo las filas y columnas de pivote SQL

La operación de pivote SQL transforma datos convirtiendo los valores de las filas en columnas. La siguiente es la sintaxis básica y estructura de pivote SQL con las siguientes partes:

• SELECT: La declaración SELECT hace referencia a las columnas que se devolverán en la tabla de pivote SQL.

• Subconsulta: La subconsulta contiene la fuente de datos o tabla que se incluirá en la tabla pivote de SQL.

• PIVOT: El operador PIVOT contiene las agregaciones y filtros que se aplicarán en la tabla dinámica.

-- Seleccionar columnas estáticas y columnas pivotadas
SELECT <static columns>, [pivoted columns]
FROM
    (
        -- Subconsulta que define los datos fuente para el pivote
        <subquery that defines data>
    ) AS source
PIVOT
(
    -- Función de agregación aplicada a la columna de valores, creando nuevas columnas
    <aggregation function>(<value column>)
    FOR <column to pivot> IN ([list of pivoted columns])
) AS pivot_table;

Veamos el siguiente ejemplo paso a paso para demostrar cómo pivotar filas a columnas en SQL. Considera la tabla SalesData a continuación.

Ejemplo de tabla para transformar usando el operador PIVOT de SQL. Imagen por Autor.

Quiero pivotar estos datos para comparar las ventas diarias de cada producto. Comenzaré seleccionando la subconsulta que estructurará el operador PIVOT.

-- Subconsulta que define los datos fuente para el pivot
SELECT Date, Product, Sales
FROM SalesData;

Ahora, usaré el operador PIVOT para convertir los valores de Producto en columnas y agregar Ventas usando el operador SUM.

-- Seleccionar Fecha y columnas pivoteadas para cada producto
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Subconsulta para obtener las columnas de Fecha, Producto y Ventas
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Agregar Ventas por Producto, pivoteando los valores de producto a columnas
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Transformación de salida de ejemplo usando pivote de SQL con filas a columnas. Imagen por Autor.

Si bien la técnica de pivote de datos simplifica el resumen de datos, esta técnica tiene problemas potenciales. A continuación se presentan los desafíos potenciales con el pivote en SQL y cómo abordarlos.

• Nombres de Columnas Dinámicos: Cuando los valores a pivotar (por ejemplo, tipos de productos) son desconocidos, el codificar los nombres de las columnas no funcionará. Algunas bases de datos, como SQL Server, admiten SQL dinámico con procedimientos almacenados para evitar este problema, mientras que otras requieren manejar esto en la capa de la aplicación.

• Lidiando con valores NULL: Cuando no hay datos para una columna pivote específica, el resultado puede incluir NULL. Puedes usar COALESCE para reemplazar los valores NULL con cero u otro marcador de posición.

• Compatibilidad entre Bases de Datos: No todas las bases de datos admiten directamente el operador PIVOT. Puedes lograr resultados similares con declaraciones CASE y agregación condicional si tu dialecto SQL no lo admite.

Convertir Filas a Columnas con SQL Pivot: Ejemplos y Casos de Uso

Se utilizan diferentes métodos para pivotar datos en SQL, dependiendo de la base de datos utilizada u otros requisitos. Mientras que el operador PIVOT se usa comúnmente en SQL Server, otras técnicas, como las declaraciones CASE, permiten transformaciones de base de datos similares sin soporte directo de PIVOT. Cubriré los dos métodos comunes de pivotar datos en SQL, y hablaré sobre los pros y los contras.

Usando el operador PIVOT

El operador PIVOT, disponible en SQL Server, proporciona una forma sencilla de pivotar filas a columnas especificando una función de agregación y definiendo las columnas a pivotar.

Considere la siguiente tabla llamada sales_data.

Ejemplo de tabla de pedidos para transformar usando el operador PIVOT. Imagen por Autor.

Usaré el operador PIVOT para agregar los datos de modo que se muestre el total de sales_revenue de cada año en columnas.

-- Usar PIVOT para agregar los ingresos de ventas por año
SELECT *
FROM (
    -- Seleccionar las columnas relevantes de la tabla fuente
    SELECT sale_year, sales_revenue
    FROM sales_data
) AS src
PIVOT (
    -- Agregar los ingresos de ventas para cada año
    SUM(sales_revenue)
    -- Crear columnas para cada año
    FOR sale_year IN ([2020], [2021], [2022], [2023])
) AS piv;

Transformación de ejemplo de salida usando SQL PIVOT. Imagen por Autor.

Usar el operador PIVOT tiene las siguientes ventajas y limitaciones:

• Ventajas: El método es eficiente cuando las columnas están correctamente indexadas. También tiene una sintaxis simple y más legible.

• Limitaciones: No todas las bases de datos admiten el operador PIVOT. Requiere especificar las columnas de antemano, y el pivoteo dinámico implica una complejidad adicional.

Pivoteo manual con declaraciones CASE

También puedes utilizar las declaraciones CASE para pivotear datos manualmente en bases de datos que no admiten operadores PIVOT, como MySQL y PostgreSQL. Este enfoque utiliza agregación condicional evaluando cada fila y asignando condicionalmente valores a nuevas columnas basándose en criterios específicos.

Por ejemplo, podemos pivotear datos manualmente en la misma tabla sales_data con declaraciones CASE.

-- Agregar los ingresos de ventas por año usando declaraciones CASE
SELECT 
    -- Calcular los ingresos totales de ventas para cada año
    SUM(CASE WHEN sale_year = 2020 THEN sales_revenue ELSE 0 END) AS sales_2020,
    SUM(CASE WHEN sale_year = 2021 THEN sales_revenue ELSE 0 END) AS sales_2021,
    SUM(CASE WHEN sale_year = 2022 THEN sales_revenue ELSE 0 END) AS sales_2022,
    SUM(CASE WHEN sale_year = 2023 THEN sales_revenue ELSE 0 END) AS sales_2023
FROM 
    sales_data;

Ejemplo de transformación de salida utilizando la declaración CASE de SQL. Imagen del autor.

Usar la declaración CASE para transformación tiene las siguientes ventajas y limitaciones:

• Ventajas: El método funciona en todas las bases de datos de SQL y es flexible para generar de forma dinámica nuevas columnas, incluso cuando los nombres de los productos son desconocidos o cambian con frecuencia.

• Limitaciones: Las consultas pueden volverse complejas y largas si hay muchas columnas para pivotar. Debido a las múltiples verificaciones condicionales, el método se ejecuta ligeramente más lento que el operador PIVOT.

Rendimiento Consideraciones al Pivotar Filas a Columnas

El pivotar filas a columnas en SQL puede tener implicaciones en el rendimiento, especialmente al trabajar con conjuntos de datos grandes. Aquí tienes algunos consejos y mejores prácticas para ayudarte a escribir consultas de pivot eficientes, optimizar su rendimiento y evitar errores comunes.

Mejores prácticas

Estas son las mejores prácticas para optimizar tus consultas y mejorar el rendimiento.

• Estrategias de indexación: La indexación adecuada es crucial para optimizar las consultas de pivote, lo que permite a SQL recuperar y procesar datos más rápido. Siempre indexa las columnas utilizadas con frecuencia en la cláusula WHERE o las columnas que estás agrupando para reducir los tiempos de escaneo.

• Avoidar Pivotes Anidados: Apilar múltiples operaciones de pivote en una consulta puede ser difícil de leer y más lento de ejecutar. Simplifique dividiendo la consulta en partes o utilizando una tabla temporal.

• Limitar Columnas y Filas en Pivote: Solo son necesarias las columnas de pivote para el análisis ya que pivotar muchas columnas puede ser intensivo en recursos y crear tablas grandes.

Avoiding common pitfalls

Estos son los errores comunes que puedes encontrar en las consultas de pivote y cómo evitarlos.

• Escaneos Completos de Tabla Innecesarios: Las consultas de pivote pueden desencadenar escaneos completos de tablas, especialmente si no hay índices relevantes disponibles. Evita los escaneos completos de tabla indexando columnas clave y filtrando los datos antes de aplicar el pivote.

• Uso de SQL Dinámico para Pivotes Frecuentes: El uso de SQL dinámico puede ralentizar el rendimiento debido a la recompilación de consultas. Para evitar este problema, cachee o limite los pivotes dinámicos a escenarios específicos y considere manejar columnas dinámicas en la capa de aplicación cuando sea posible.

• Agregación en conjuntos de datos grandes sin prefiltrado: Las funciones de agregación como SUM o COUNT en conjuntos de datos grandes pueden ralentizar el rendimiento de la base de datos. En lugar de pivotar todo el conjunto de datos, filtre los datos primero utilizando una cláusula WHERE.

• Valores NULL en columnas pivotadas: Las operaciones de pivote a menudo producen valores NULL cuando no hay datos para una columna específica. Estos pueden ralentizar las consultas y hacer que los resultados sean más difíciles de interpretar. Para evitar este problema, utiliza funciones como COALESCE para reemplazar los valores NULL con un valor predeterminado.

• Pruebas solo con Datos de Ejemplo: Las consultas de pivote pueden comportarse de manera diferente con conjuntos de datos grandes debido a la demanda aumentada de memoria y procesamiento. Siempre pruebe las consultas de pivote en datos reales o muestras representativas para evaluar con precisión los impactos en el rendimiento.

Prueba nuestra trayectoria profesional de SQL Server Developer, que cubre todo, desde transacciones y manejo de errores hasta mejorar el rendimiento de consultas.

Implementaciones Específicas de Bases de Datos

Las operaciones de pivote difieren significativamente entre bases de datos como SQL Server, MySQL y Oracle. Cada una de estas bases de datos tiene una sintaxis y limitaciones específicas. Cubriré ejemplos de pivotar datos en las diferentes bases de datos y sus características clave.

SQL Server

SQL Server proporciona un operador PIVOT incorporado, que es sencillo al pivotar filas a columnas. El operador PIVOT es fácil de usar e se integra con las potentes funciones de agregación de SQL Server. Las características clave del pivote en SQL incluyen lo siguiente:

• Soporte directo para PIVOT y UNPIVOT: El operador PIVOT de SQL Server permite una rápida transformación de filas a columnas. El operador UNPIVOT también puede revertir este proceso.

• Opciones de Agregación: El operador PIVOT permite varias funciones de agregación, como SUM, COUNT y AVG.

La limitación del operador PIVOT en SQL Server es que requiere que los valores de las columnas a pivotar sean conocidos de antemano, lo que lo hace menos flexible para datos que cambian dinámicamente.

En el ejemplo a continuación, el operador PIVOT convierte los valores de Product en columnas y agrega Sales utilizando el operador SUM.

-- Seleccionar Fecha y columnas pivotadas para cada producto
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Subconsulta para obtener las columnas Fecha, Producto y Ventas
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Agregar Ventas por Producto, pivotando los valores de producto a columnas
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Te recomiendo tomar el curso Introducción a SQL Server de DataCamp para dominar los conceptos básicos de SQL Server para el análisis de datos.

MySQL

MySQL carece de soporte nativo para el operador PIVOT. Sin embargo, puedes usar la instrucción CASE para pivotar manualmente filas a columnas y combinar otras funciones de agregación como SUM, AVG y COUNT. Aunque este método es flexible, puede volverse complejo si tienes muchas columnas que pivotar.

La consulta a continuación logra el mismo resultado que el ejemplo de PIVOT de SQL Server, agregando condicionalmente las ventas de cada producto utilizando la declaración CASE.

-- Seleccionar fecha y columnas pivotadas para cada producto
SELECT 
    Date,
    -- Utilizar CASE para crear una columna para las ventas de Laptop y Mouse
    SUM(CASE WHEN Product = 'Laptop' THEN Sales ELSE 0 END) AS Laptop,
    SUM(CASE WHEN Product = 'Mouse' THEN Sales ELSE 0 END) AS Mouse
FROM SalesData
GROUP BY Date;

Oracle

Oracle admite el operador PIVOT, que permite la transformación directa de filas en columnas. Al igual que SQL Server, deberá especificar explícitamente las columnas para la transformación.

En la consulta a continuación, el operador PIVOT convierte los valores de ProductName en columnas y agrega SalesAmount utilizando el operador SUM.

SELECT *
FROM (
    -- Selección de datos fuente
    SELECT SaleDate, ProductName, SaleAmount FROM SalesData
)
PIVOT (
    -- Agregar ventas por producto, creando columnas pivotadas
    SUM(SaleAmount)
    FOR ProductName IN ('Laptop' AS Laptop, 'Mouse' AS Mouse)
);

Transformación de ejemplo de salida utilizando el operador PIVOT en SQL en Oracle. Imagen por el Autor.

Técnicas Avanzadas para Convertir Filas en Columnas en SQL

Técnicas avanzadas para convertir filas en columnas son útiles cuando se necesita flexibilidad para manejar datos complejos. Las técnicas dinámicas y el manejo de múltiples columnas simultáneamente te permiten transformar datos en escenarios donde la pivoteación estática es limitada. Vamos a explorar estos dos métodos en detalle.

Pivotes dinámicos

Los pivotes dinámicos te permiten crear consultas de pivote que se adaptan automáticamente a los cambios en los datos. Esta técnica es particularmente útil cuando tienes columnas que cambian con frecuencia, como nombres de productos o categorías, y deseas que tu consulta incluya automáticamente nuevas entradas sin necesidad de actualizarla manualmente.

Supongamos que tenemos una tabla SalesData y podemos crear un pivote dinámico que se ajusta si se agregan nuevos productos. En la consulta a continuación, @columns construye dinámicamente la lista de columnas pivotadas, y sp_executesql ejecuta el SQL generado.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
-- Paso 1: Generar una lista de productos distintos para pivotar
SELECT @columns = STRING_AGG(QUOTENAME(Product), ', ') 
FROM (SELECT DISTINCT Product FROM SalesData) AS products;
-- Paso 2: Construir la consulta SQL dinámica
SET @sql = N'
SELECT Date, ' + @columns + '
FROM 
    (SELECT Date, Product, Sales FROM SalesData) AS source
PIVOT
(
    SUM(Sales)
    FOR Product IN (' + @columns + ')
) AS pivot_table;';
-- Paso 3: Ejecutar el SQL dinámico
EXEC sp_executesql @sql;

Manejo de múltiples columnas

En escenarios donde necesitas pivotar múltiples columnas simultáneamente, utilizarás el operador PIVOT y técnicas de agregación adicionales para crear múltiples columnas en la misma consulta.

En el ejemplo a continuación, he pivotado las columnas Sales y Quantity por Product.

-- Ventas y cantidad de unidades para portátiles y ratones por fecha
SELECT 
    p1.Date, 
    p1.[Laptop] AS Laptop_Sales, 
    p2.[Laptop] AS Laptop_Quantity, 
    p1.[Mouse] AS Mouse_Sales, 
    p2.[Mouse] AS Mouse_Quantity
FROM 
    (
        -- Pivote para ventas
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Sales FROM SalesData) AS source
        PIVOT
            (SUM(Sales) FOR Product IN ([Laptop], [Mouse])) AS pivot_sales
    ) p1
JOIN
    (
        -- Pivote para cantidad
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Quantity FROM SalesData) AS source
        PIVOT
            (SUM(Quantity) FOR Product IN ([Laptop], [Mouse])) AS pivot_quantity
    ) p2
ON p1.Date = p2.Date;

Transformación de ejemplo de salida de múltiples columnas utilizando el operador PIVOT de SQL. Imagen por el autor.

La realización de pivotes de múltiples columnas permite obtener informes más detallados al pivotar múltiples atributos por artículo, lo que permite obtener información más completa. Sin embargo, la sintaxis puede ser compleja, especialmente si existen muchas columnas. Puede ser necesario codificar manualmente a menos que se combine con técnicas de pivote dinámico, lo que añade una mayor complejidad.

Conclusión

La técnica de pivotar filas a columnas en SQL es digna de aprender. He visto técnicas de pivot en SQL utilizadas para crear una tabla de retención de cohortes, donde se puede hacer un seguimiento de la retención de usuarios a lo largo del tiempo. También he visto técnicas de pivot en SQL utilizadas para analizar datos de encuestas, donde cada fila representa a un encuestado y cada pregunta se puede pivotar en su propia columna.

Nuestro curso Reporting en SQL es una excelente opción si desea aprender más sobre resumir y preparar datos para su presentación y/o crear paneles de control. Nuestras trayectorias profesionales Analista de Datos Asociado en SQL y Ingeniero de Datos Asociado en SQL son otra gran idea, y agregan mucho a cualquier currículum, así que inscríbase hoy mismo.