Saltar al contenido principal

Cómo utilizar SQL PIVOT

Mejora tus habilidades SQL con el operador SQL PIVOT. Aprende a convertir filas en columnas para crear tablas dinámicas en SQL Server y Oracle.
Actualizado 29 jul 2024  · 10 min de lectura

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

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

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 las cláusulas WHERE, GROUP BY y ORDER BY

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 un PIVOT dinámico SQL

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.

Preguntas frecuentes

¿Qué es SQL PIVOT?

El operador SQL PIVOT transforma filas en columnas en los resultados de la consulta.

¿Qué bases de datos admiten SQL PIVOT?

SQL Server y Oracle proporcionan soporte nativo para el operador PIVOT. MySQL y PostgreSQL crean tablas dinámicas utilizando agregaciones y sentencias CASE.

¿En qué se diferencia PIVOT de UNPIVOT?

El operador PIVOT se utiliza para transformar filas de datos en columnas agregándolas para hacerlas legibles. La cláusula UNPIVOT se utiliza para transformar columnas en filas.

Si utilizas PIVOT con agregación y luego UNPIVOT, ¿recuperas los datos a su forma original?

No, utilizar PIVOT con una agregación y luego aplicar UNPIVOT no suele ser una operación inversa exacta.

¿Puedo pivotar datos dinámicamente en SQL?

SQL Server y PostgreSQL admiten el pivotaje dinámico. MySQL permite el pivotaje dinámico mediante procedimientos almacenados.

¿Se puede combinar PIVOT con cláusulas SQL?

Puedes combinar el operador PIVOT con cláusulas SQL para filtrar datos, incluidas las cláusulas WHERE, GROUP BY y ORDER BY.

¿Las tabulaciones cruzadas son lo mismo que las tablas dinámicas en SQL?

Sí, las tabulaciones cruzadas (tabulaciones cruzadas) y las tablas dinámicas en SQL son esencialmente el mismo concepto. Ambos se utilizan para resumir y reorganizar los datos para hacerlos más accesibles.

Temas

Aprende SQL con DataCamp

curso

Introduction to SQL Server

4 hr
151.4K
Learn to use SQL Server to perform common data manipulation tasks and master common data manipulation tasks using this database system.
Ver detallesRight Arrow
Comienza el curso
Ver másRight Arrow
Relacionado

blog

10 proyectos SQL listos para la cartera para todos los niveles

Selecciona tu primer -o siguiente- proyecto SQL para practicar tus habilidades SQL actuales, desarrollar otras nuevas y crear una cartera profesional sobresaliente.
Elena Kosourova's photo

Elena Kosourova

11 min

tutorial

Seleccionar varias columnas en SQL

Aprende a seleccionar fácilmente varias columnas de una tabla de base de datos en SQL, o a seleccionar todas las columnas de una tabla en una simple consulta.
DataCamp Team's photo

DataCamp Team

3 min

tutorial

Creación y personalización de tablas dinámicas en Power BI

Aprende a crear tablas dinámicas personalizables en Power BI con formato condicional avanzado y algunos consejos de optimización.
Joleen Bothma's photo

Joleen Bothma

9 min

tutorial

Cómo utilizar un alias SQL para simplificar tus consultas

Explora cómo el uso de un alias SQL simplifica tanto los nombres de las columnas como los de las tablas. Aprende por qué utilizar un alias SQL es clave para mejorar la legibilidad y gestionar uniones complejas.
Allan Ouko's photo

Allan Ouko

9 min

tutorial

Cómo utilizar GROUP BY y HAVING en SQL

Una guía intuitiva para descubrir los dos comandos SQL más populares para agregar filas de tu conjunto de datos
Eugenia Anello's photo

Eugenia Anello

6 min

tutorial

Ejemplos y tutoriales de consultas SQL

Si quiere iniciarse en SQL, nosotros le ayudamos. En este tutorial de SQL, le presentaremos las consultas SQL, una potente herramienta que nos permite trabajar con los datos almacenados en una base de datos. Verá cómo escribir consultas SQL, aprenderá sobre
Sejal Jaiswal's photo

Sejal Jaiswal

21 min

Ver másVer más