Cómo utilizar SQL PIVOT

El operador PIVOT en SQL Server y Oracle es una técnica extremadamente útil que transforma filas de tabla en columnas. El operador PIVOT no sólo mejora la legibilidad y la interpretación de los resultados de la consulta, sino que también facilita la comprensión de las tendencias de los datos utilizando agregaciones para crear tablas dinámicas o tabulaciones cruzadas. Estas tablas dinámicas son especialmente útiles en informes que requieren visualizaciones agradables.

Si eres un aspirante a analista de datos que busca afianzarse en el sector o un analista más experimentado, te recomiendo que sigas los cursos Introducción a SQL y SQL Intermedio de DataCamp para mejorar tus conocimientos de análisis de datos. También te recomiendo que sigas nuestro curso Manipulación de datos en SQL, en el que se enseñan las subconsultas y otros conceptos tratados en este tutorial, junto con nuestro curso Introducción a SQL Server, que trata específicamente de SQL Server.

La respuesta rápida: Cómo hacer pivotes en SQL

El operador PIVOT de SQL Server es útil a la hora de resumir datos, ya que permite transformar filas en columnas. Considera la siguiente tabla de city_sales, que muestra las ventas generales de un producto en cinco grandes ciudades de EEUU.

Ejemplo de tabla a transformar utilizando SQL PIVOT. Imagen del autor.

Utilizaremos la siguiente consulta, que utiliza el operador PIVOT, para hacer pivotar varias columnas de la tabla anterior.

-- Select the columns for the output: city and sales data for 2019, 2020, and 2021
SELECT 
    city,
    [2019] AS Sales_2019,
    [2020] AS Sales_2020,
    [2021] AS Sales_2021
FROM
    (
        -- Subquery to select city, year, and sales from city_sales table
        SELECT city, year, sales 
        FROM city_sales
    ) AS src
PIVOT
(
    -- Pivot the sales data to have years as columns and sum the sales for each year
    SUM(sales)
    FOR year IN ([2019], [2020], [2021])
) AS pvt;

Ejemplo de transformación de salida utilizando SQL PIVOT. Imagen del autor.

Qué es PIVOT en SQL

Pivotar es una técnica de SQL que se utiliza para transformar filas en columnas en datos tabulares. En SQL Server y Oracle, el pivotaje se realiza con el operador PIVOT. La sintaxis del operador SQL PIVOT, que se muestra a continuación, consta de tres partes principales:

• SELECCIONA: La sentencia SELECT hace referencia a las columnas a devolver en la tabla pivotante SQL.

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

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

-- Select the non-pivoted column and the pivoted columns with aliases
SELECT 
    [non-pivoted column],               
    [first pivoted column] AS [column name],   
    [second pivoted column] AS [column name],  
    ...
FROM
    (
        -- Subquery to select the necessary columns from the source table
        SELECT [columns] 
        FROM [source_table]
    ) AS source_table
PIVOT
(
    -- Pivot operation to aggregate data and transform rows into columns
    [aggregate_function]([pivot_column])        
    FOR [pivot_column] IN ([first pivoted column], [second pivoted column], ...) 
) AS pivot_table; -- Alias for the result of the pivot operation

Implementaciones de SQL PIVOT específicas para cada base de datos

Las bases de datos SQL Server y Oracle admiten directamente el operador PIVOT. Sin embargo, MySQL y PostgreSQL tienen métodos alternativos para crear tablas dinámicas en SQL.

PIVOT en SQL Server

SQL Server proporciona soporte nativo para el operador PIVOT. Aquí utilizaremos el operador PIVOT para transformar filas en columnas y resumir datos utilizando funciones agregadas como SUM(). También utilizaremos cláusulas SQL, como WHERE, GROUP BY, y ORDER BY para una manipulación más refinada de los datos.

El siguiente ejemplo muestra cómo utilizar el operador PIVOT para filtrar los datos del año 2020 o posterior (WHERE), agrupar los datos por ciudad y año (GROUP BY), y ordenar los datos por ciudad (ORDER BY):

-- Select the city and sales data for the years 2019, 2020, and 2021
SELECT 
    city,                         
    [2019] AS Sales_2019,             
    [2020] AS Sales_2020,           
    [2021] AS Sales_2021              
FROM
    (
        -- Subquery to select city, year, and sales from the city_sales table
        SELECT city, year, sales 
        FROM city_sales
        WHERE year >= 2020 -- filtering
        GROUP BY city, year, sales -- grouping
    ) AS src                   
PIVOT
(
    -- Pivot the sales data to have years as columns, averaging the sales over each year
    SUM(sales) -- aggregating
    FOR year IN ([2019], [2020], [2021]) 
) AS pvt;         

Ejemplo de salida de tabla transformada utilizando SQL PIVOT con cláusulas comunes. Imagen del autor.

PIVOT en Oracle

Al igual que SQL Server, Oracle también utiliza el operador PIVOT para transformar filas en columnas. Sin embargo, la sintaxis del operador PIVOT en la base de datos Oracle difiere ligeramente de la de SQL Server. La consulta siguiente muestra cómo aparece el operador PIVOT en Oracle. Ten en cuenta que las columnas tienen un alias dentro del operador PIVOT, a diferencia de la sentencia externa SELECT de SQL Server.

-- Outer SELECT to choose all columns resulting from the PIVOT operation
SELECT *
FROM (
    -- Inner SELECT to retrieve the raw data of city, year, and sales
    SELECT city, year, sales
    FROM sales
)
-- PIVOT operation to convert rows to columns
PIVOT (
    SUM(sales) 
    -- Specify the year values to pivot and alias them as Sales_<year>
    FOR year IN (2019 AS Sales_2019, 2020 AS Sales_2020, 2021 AS Sales_2021)
)
ORDER BY city;

Pivotar en MySQL

La base de datos MySQL no admite el operador SQL PIVOT. Para crear tablas dinámicas SQL en MySQL, debes utilizar la sentencia CASE con agregación condicional. Por ejemplo, la consulta siguiente creará una tabla dinámica para agregar los datos por la suma de las ventas de los distintos años, agrupados y ordenados por city.

-- Select the city and sum the sales data for the years 2019, 2020, and 2021
SELECT 
    city,
    SUM(CASE WHEN year = 2019 THEN sales ELSE 0 END) AS Sales_2019,
    SUM(CASE WHEN year = 2020 THEN sales ELSE 0 END) AS Sales_2020,
    SUM(CASE WHEN year = 2021 THEN sales ELSE 0 END) AS Sales_2021
FROM 
    city_sales 
GROUP BY 
    city
ORDER BY 
    city; 

Pivotar en PostgreSQL

La base de datos PostgreSQL tampoco admite el operador SQL PIVOT. Por tanto, al crear tablas dinámicas, es importante utilizar la sentencia CASE con agregación condicional. La consulta siguiente es un ejemplo de las sentencias condicionales CASE utilizadas para crear tablas pivotantes en PostgreSQL.

-- Select the city and sum the sales data for the years 2019, 2020, and 2021
SELECT 
    city,
    SUM(CASE WHEN year = 2019 THEN sales ELSE 0 END) AS Sales_2019, 
    SUM(CASE WHEN year = 2020 THEN sales ELSE 0 END) AS Sales_2020,
    SUM(CASE WHEN year = 2021 THEN sales ELSE 0 END) AS Sales_2021
FROM 
    city_sales
GROUP BY 
    city
ORDER BY 
    city; 

Técnicas avanzadas con SQL PIVOT

Existen algunas técnicas avanzadas de pivotaje SQL para escribir consultas complejas. En esta sección, veremos el pivotaje dinámico, que utilizamos en para crear consultas para tablas pivotantes en las que se desconocen las columnas a pivotar. Este método utiliza SQL para generar la tabla dinámica en tiempo de ejecución.

PIVOT dinámico en SQL Server

La siguiente consulta utiliza PIVOT para pivotar dinámicamente la columna year en SQL Server. La consulta recuperará años distintos de la tabla city_sales. A continuación, construirá y ejecutará una consulta dinámica PIVOT utilizando los años recuperados.

-- Declare variables to hold the column names and the dynamic query
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);
-- Get distinct values of the year column and concatenate them into a string
SELECT @cols = STRING_AGG(QUOTENAME(year), ',') 
               FROM (SELECT DISTINCT year FROM city_sales) AS years;
-- Construct the dynamic PIVOT query
SET @query = '
SELECT city, ' + @cols + '
FROM
(
    -- Subquery to select city, year, and sales from the city_sales table
    SELECT city, year, sales
    FROM city_sales
) AS src
PIVOT
(
    -- Pivot the sales data to have years as columns, summing the sales for each year
    SUM(sales)
    FOR year IN (' + @cols + ')
) AS pvt
ORDER BY city';  -- Order the results by city
-- Execute the dynamic PIVOT query
EXEC sp_executesql @query;

Ejemplo de salida de tabla utilizando PIVOT dinámico SQL. Imagen del autor.

PIVOT dinámico en Oracle

En la base de datos Oracle, el pivotaje dinámico se soporta ejecutando la consulta dinámica mediante la sentencia EXECUTE IMMEDIATE. La función LISTAGG también se utiliza para agregar dinámicamente los nombres de las columnas y las comillas simples ' ' utilizadas en los alias dentro del pivote.

DECLARE
    cols VARCHAR2(4000);
    sql_query VARCHAR2(4000);
BEGIN
    -- Get the list of years dynamically
    SELECT LISTAGG('''' || year || ''' AS ' || 'sales_' || year, ',') 
    INTO cols
    FROM (SELECT DISTINCT year FROM city_sales);
    -- Construct the dynamic SQL query
    sql_query := 'SELECT * FROM (
                      SELECT city, year, sales
                      FROM city_sales
                  )
                  PIVOT (
                      SUM(sales)
                      FOR year IN (' || cols || ')
                  )
                  ORDER BY city';
    -- Execute the dynamic SQL query
    EXECUTE IMMEDIATE sql_query;
END;

Pivotaje dinámico en MySQL

MySQL no admite SQL dinámico directo. Por tanto, debes crear un procedimiento almacenado para PIVOT dinámico en MySQL. La consulta siguiente muestra cómo utilizar el procedimiento almacenado para crear una consulta dinámica PIVOT.

-- Declare variables to hold the dynamic columns (cols) and the final SQL query
DELIMITER $
CREATE PROCEDURE dynamic_pivot()
BEGIN
    DECLARE cols VARCHAR(1000);
    DECLARE sql_query VARCHAR(2000);
    -- Get the list of distinct years
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT('SUM(CASE WHEN year = ', year, ' THEN sales ELSE 0 END) AS ', year, '')
    ) INTO cols
    FROM city_sales;
    -- Construct the dynamic SQL query
    SET sql_query = CONCAT('SELECT city, ', cols, ' FROM city_sales GROUP BY city ORDER BY city');
    -- Prepare and execute the SQL query
    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $
DELIMITER ;

Después de crear el procedimiento almacenado, debes llamarlo para ejecutar la consulta dinámica PIVOT:

CALL dynamic_pivot();

Pivotaje dinámico en PostgreSQL

Del mismo modo, puedes crear un PIVOT dinámico en PostgreSQL utilizando la siguiente consulta:

-- Block declaration to execute PL/pgSQL code in an anonymous code block
DO
$
DECLARE
    cols text;  -- Variable to store the list of columns for the dynamic query
    query text; -- Variable to store the dynamic SQL query
BEGIN
    -- Get distinct years and construct the list of SUM(CASE...) statements
    SELECT STRING_AGG(DISTINCT 'SUM(CASE WHEN year = ' || year || ' THEN sales ELSE 0 END) AS "Sales_' || year || '"', ', ')
    INTO cols
    FROM city_sales;
    -- Construct the dynamic PIVOT query
    query := 'SELECT city, ' || cols || ' FROM city_sales GROUP BY city ORDER BY city';
    -- Execute the dynamic PIVOT query
    EXECUTE query;
END
$;

Conclusión y aprendizaje posterior

Comprender cómo utilizar PIVOT en SQL es importante si quieres transformar y analizar datos con eficacia. Al crear tablas pivotantes en SQL, es crucial aprender las distintas implementaciones del operador PIVOT en las diferentes bases de datos. Como analista de datos, te animo a que sigas practicando tus conocimientos de SQL para aprender cómo y cuándo aplicar PIVOT para analizar distintos conjuntos de datos.

Te recomiendo encarecidamente que consultes el tema de habilidades Fundamentos de SQL de DataCamp para comprender las habilidades que necesitas para ser avanzado en SQL. Del mismo modo, deberías echar un vistazo a nuestro itinerario profesional de Analista de Datos Asociado en SQL para ayudarte a mantener el progreso del SQL necesario que necesitarás en tu carrera como analista de datos.