Como usar o SQL PIVOT

O operador PIVOT no SQL Server e no Oracle é uma técnica extremamente útil que transforma as linhas da tabela em colunas. O operador PIVOT não só melhora a legibilidade e a interpretação dos resultados da consulta, como também facilita a compreensão das tendências dos dados, usando agregações para criar tabelas dinâmicas ou tabulações cruzadas. Essas tabelas dinâmicas são particularmente úteis em relatórios que exigem boas visualizações.

Se você é um aspirante a analista de dados que deseja se firmar no setor ou um analista mais experiente, recomendo que faça os cursos de Introdução ao SQL e SQL Intermediário da DataCamp para aprimorar suas habilidades de análise de dados. Também recomendo que você faça nosso curso Manipulação de dados em SQL, que ensina subconsultas e outros conceitos abordados neste tutorial, juntamente com nosso curso Introdução ao SQL Server, que aborda especificamente o SQL Server.

A resposta rápida: Como dinamizar no SQL

O operador PIVOT do SQL Server é útil ao resumir dados, pois permite a transformação de linhas em colunas. Considere a tabela city_sales abaixo, que mostra as vendas gerais de um produto em cinco grandes cidades dos EUA.

Exemplo de tabela a ser transformada usando SQL PIVOT. Imagem do autor.

Usaremos a seguinte consulta, que usa o operador PIVOT, para dinamizar várias colunas na tabela acima.

-- 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;Tecnologia

Exemplo de transformação de saída usando SQL PIVOT. Imagem do autor.

O que é PIVOT no SQL?

A dinamização é uma técnica do SQL usada para transformar linhas em colunas em dados tabulares. No SQL Server e no Oracle, a dinamização é feita com o operador PIVOT. A sintaxe do operador SQL PIVOT, que é mostrada abaixo, tem três partes principais:

• SELECIONAR: A instrução SELECT faz referência às colunas que você deve retornar na tabela dinâmica do SQL.

• Subconsulta: A subconsulta contém a fonte de dados ou a tabela a ser incluída na tabela dinâmica SQL.

• PIVÔ: O operador PIVOT contém as agregações e o filtro a serem aplicados na tabela 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 operationTecnologia

Implementações específicas de banco de dados do SQL PIVOT

Os bancos de dados SQL Server e Oracle suportam diretamente o operador PIVOT. No entanto, o MySQL e o PostgreSQL têm métodos alternativos para criar tabelas dinâmicas no SQL.

PIVOT no SQL Server

O SQL Server oferece suporte nativo para o operador PIVOT. Aqui, usaremos o operador PIVOT para transformar linhas em colunas e resumir dados usando funções agregadas como SUM(). Também usaremos cláusulas SQL, como WHERE, GROUP BY e ORDER BY para uma manipulação de dados mais refinada.

O exemplo abaixo demonstra como você pode usar o operador PIVOT para filtrar os dados do ano de 2020 ou posterior (WHERE), agrupar os dados por cidade e ano (GROUP BY) e classificar os dados por cidade (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;         Tecnologia

Exemplo de saída de tabela transformada usando SQL PIVOT com cláusulas comuns. Imagem do autor.

PIVOT no Oracle

Assim como o SQL Server, o Oracle também usa o operador PIVOT para transformar linhas em colunas. No entanto, a sintaxe do operador PIVOT no banco de dados Oracle é um pouco diferente da do SQL Server. A consulta abaixo mostra como o operador PIVOT aparece no Oracle. Observe que as colunas são aliases dentro do operador PIVOT, diferentemente da instrução SELECT externa no 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;Tecnologia

Pivotamento no MySQL

O banco de dados MySQL não é compatível com o operador SQL PIVOT. Para criar tabelas dinâmicas SQL no MySQL, você deve usar a instrução CASE com agregação condicional. Por exemplo, a consulta abaixo criará uma tabela dinâmica para agregar os dados pela soma das vendas de diferentes anos, agrupados e 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; Tecnologia

Pivotamento no PostgreSQL

O banco de dados PostgreSQL também não é compatível com o operador SQL PIVOT. Portanto, ao criar tabelas dinâmicas, é importante que você use a instrução CASE com agregação condicional. A consulta abaixo é um exemplo das instruções condicionais CASE usadas para criar tabelas dinâmicas no 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; Tecnologia

Técnicas avançadas com o SQL PIVOT

Existem algumas técnicas avançadas de pivotagem SQL para que você possa escrever consultas complexas. Nesta seção, veremos a dinamização dinâmica, que usamos no site para criar consultas para tabelas dinâmicas em que as colunas a serem dinamizadas são desconhecidas. Esse método usa SQL para gerar a tabela dinâmica em tempo de execução.

PIVOT dinâmico no SQL Server

A consulta abaixo usa o site PIVOT para dinamicamente dinamizar a coluna year no SQL Server. A consulta recuperará anos distintos da tabela city_sales. Em seguida, você poderá construir e executar uma consulta dinâmica PIVOT usando os anos 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;Tecnologia

Exemplo de saída de tabela usando PIVOT dinâmico SQL. Imagem do autor.

PIVOT dinâmico no Oracle

No banco de dados Oracle, a dinamização dinâmica é suportada pela execução da consulta dinâmica usando a instrução EXECUTE IMMEDIATE. A função LISTAGG também é usada para agregar dinamicamente os nomes das colunas e as aspas simples ' ' usadas em aliases dentro do pivô.

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;Tecnologia

Pivotamento dinâmico no MySQL

O MySQL não oferece suporte a SQL dinâmico direto. Portanto, você deve criar um procedimento armazenado para PIVOT dinâmico no MySQL. A consulta abaixo mostra como você pode usar o procedimento armazenado para criar uma consulta dinâmica no site 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 ;Tecnologia

Depois de criar o procedimento armazenado, você deve chamar o procedimento armazenado para executar a consulta dinâmica PIVOT:

CALL dynamic_pivot();Tecnologia

Pivotamento dinâmico no PostgreSQL

Da mesma forma, você pode criar um PIVOT dinâmico no PostgreSQL usando a seguinte 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
$;Tecnologia

Conclusão e aprendizado adicional

Entender como usar o PIVOT no SQL é importante se você quiser transformar e analisar dados com eficiência. Ao criar tabelas dinâmicas no SQL, é crucial que você conheça as diferentes implementações do operador PIVOT nos diferentes bancos de dados. Como analista de dados, incentivo você a continuar praticando suas habilidades em SQL para saber como e quando aplicar o PIVOT para analisar diferentes conjuntos de dados.

Recomendo enfaticamente que você consulte o programa de habilidades SQL Fundamentals da DataCamp para entender as habilidades necessárias para avançar no SQL. Da mesma forma, você deve conferir nosso programa de carreira Associate Data Analyst in SQL para ajudá-lo a manter o progresso do SQL necessário em sua carreira como analista de dados.