SQL Girar Linhas para Colunas: Um Guia Abrangente

O Que Significa Pivoteamento de Linhas para Colunas no SQL?

O pivoting no SQL refere-se à transformação de dados de um formato baseado em linhas para um formato baseado em colunas. Essa transformação é útil para relatórios e análises de dados, permitindo uma visualização mais estruturada e compacta dos dados. A pivotação de linhas para colunas também permite aos usuários analisar e resumir dados de maneira que destaque insights importantes de forma mais clara.

Considere o seguinte exemplo: Tenho uma tabela com transações diárias de vendas, e cada linha registra a data, o nome do produto e o valor das vendas.

Ao realizar a pivotação dessa tabela, posso reestruturá-la para mostrar cada produto como uma coluna, com os dados de vendas para cada data sob sua coluna correspondente. Observe também que ocorre uma agregação.

Tradicionalmente, operações de pivô requeriam consultas SQL complexas com agregação condicional. Com o tempo, as implementações SQL evoluíram, com muitos bancos de dados modernos agora incluindo os operadores PIVOT e UNPIVOT para permitir transformações mais eficientes e diretas.

Entendendo Linhas para Colunas no SQL Pivot

A operação de pivô no SQL transforma os dados, convertendo os valores das linhas em colunas. A seguir está a sintaxe básica e a estrutura do SQL pivot com as seguintes partes:

• SELECT: A instrução SELECT faz referência às colunas a serem retornadas na tabela pivot do SQL.

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

• PIVOT: O operador PIVOT contém as agregações e filtros a serem aplicados na tabela dinâmica.

-- Selecionar colunas estáticas e colunas pivotadas
SELECT <static columns>, [pivoted columns]
FROM
    (
        -- Subconsulta definindo os dados de origem para o pivot
        <subquery that defines data>
    ) AS source
PIVOT
(
    -- Função de agregação aplicada à coluna de valores, criando novas colunas
    <aggregation function>(<value column>)
    FOR <column to pivot> IN ([list of pivoted columns])
) AS pivot_table;

Vamos analisar o exemplo passo a passo a seguir para demonstrar como pivotar linhas para colunas no SQL. Considere a tabela SalesData abaixo.

Exemplo de tabela a ser transformada usando o operador PIVOT do SQL. Imagem por Autor.

Quero pivotar esses dados para comparar as vendas diárias de cada produto. Vou começar selecionando a subconsulta que estruturará o operador PIVOT.

-- Subconsulta definindo os dados de origem para o pivot
SELECT Date, Product, Sales
FROM SalesData;

Agora, vou usar o operador PIVOT para converter os valores de Produto em colunas e agregar as Vendas usando o operador SUM.

-- Selecionar Data e colunas pivotadas para cada produto
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Subconsulta para buscar as colunas Data, Produto e Vendas
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Agregar Vendas por Produto, pivotando os valores do produto para colunas
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Exemplo de transformação de saída usando pivot SQL para transformar linhas em colunas. Imagem por Autor.

Ao pivotar dados, simplifica-se o resumo de dados, porém essa técnica apresenta problemas potenciais. A seguir estão os desafios potenciais com o pivot SQL e como resolvê-los.

• Nomes de Colunas Dinâmicas: Quando os valores a serem pivotados (por exemplo, tipos de produtos) são desconhecidos, a codificação rígida dos nomes das colunas não funcionará. Alguns bancos de dados, como o SQL Server, suportam SQL dinâmico com stored procedures para evitar esse problema, enquanto outros exigem lidar com isso na camada de aplicação.

• Tratando com Valores NULL: Quando não há dados para uma coluna específica pivotada, o resultado pode incluir NULL. Você pode usar COALESCE para substituir os valores NULL por zero ou outro marcador.

• Compatibilidade entre Bancos de Dados: Nem todos os bancos de dados suportam diretamente o operador PIVOT. Você pode obter resultados semelhantes com declarações CASE e agregação condicional se seu dialeto SQL não o suportar.

Exemplos e Casos de Uso do Pivot de Linhas para Colunas em SQL

Métodos diferentes são usados para pivotar dados em SQL, dependendo do banco de dados utilizado ou de outros requisitos. Enquanto o operador PIVOT é comumente usado no SQL Server, outras técnicas, como as instruções CASE, permitem transformações semelhantes no banco de dados sem suporte direto ao PIVOT. Vou cobrir os dois métodos comuns de pivotar dados em SQL e falar sobre os prós e contras.

Usando o operador PIVOT

O operador PIVOT, disponível no SQL Server, fornece uma maneira direta de pivotar linhas em colunas especificando uma função de agregação e definindo as colunas a serem pivotadas.

Considere a seguinte tabela chamada sales_data.

Exemplo de tabela de Pedidos para transformar usando o operador PIVOT. Imagem do Autor.

Vou usar o operador PIVOT para agregar os dados de forma que o total de sales_revenue de cada ano seja mostrado em colunas.

-- Usar PIVOT para agregar a receita de vendas por ano
SELECT *
FROM (
    -- Selecionar as colunas relevantes da tabela de origem
    SELECT sale_year, sales_revenue
    FROM sales_data
) AS src
PIVOT (
    -- Agregar a receita de vendas para cada ano
    SUM(sales_revenue)
    -- Criar colunas para cada ano
    FOR sale_year IN ([2020], [2021], [2022], [2023])
) AS piv;

Exemplo de transformação de saída usando SQL PIVOT. Imagem por Autor.

O uso do operador PIVOT possui as seguintes vantagens e limitações:

• Vantagens: O método é eficiente quando as colunas estão devidamente indexadas. Ele também possui uma sintaxe simples e mais legível.

• Limitações: Nem todos os bancos de dados suportam o operador PIVOT. É necessário especificar as colunas antecipadamente e o pivoteamento dinâmico requer complexidade adicional.

Pivotação manual com declarações CASE

Você também pode usar as declarações CASE para pivotar manualmente os dados em bancos de dados que não suportam operadores PIVOT, como o MySQL e o PostgreSQL. Esta abordagem utiliza agregação condicional avaliando cada linha e atribuindo condicionalmente valores a novas colunas com base em critérios específicos.

Por exemplo, podemos pivotar manualmente os dados na mesma tabela sales_data com declarações CASE.

-- Agregar a receita de vendas por ano usando declarações CASE
SELECT 
    -- Calcular a receita total de vendas para cada ano
    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;

Exemplo de transformação de saída usando a declaração SQL CASE. Imagem por Autor.

O uso da declaração CASE para transformação tem as seguintes vantagens e limitações:

• Vantagens: O método funciona em todos os bancos de dados SQL e é flexível para gerar dinamicamente novas colunas, mesmo quando os nomes dos produtos são desconhecidos ou mudam com frequência.

• Limitações: As consultas podem se tornar complexas e extensas se houver muitas colunas para pivotar. Devido às múltiplas verificações condicionais, o método tem um desempenho ligeiramente mais lento do que o operador PIVOT.

Considerações de Desempenho ao Transformar Linhas em Colunas

A transformação de linhas em colunas no SQL pode ter implicações de desempenho, especialmente ao trabalhar com grandes conjuntos de dados. Aqui estão algumas dicas e melhores práticas para ajudá-lo a escrever consultas de pivot eficientes, otimizar seu desempenho e evitar armadilhas comuns.

Práticas recomendadas

As seguintes são as melhores práticas para otimizar suas consultas e melhorar o desempenho.

• Estratégias de Indexação: A indexação adequada é crucial para otimizar as consultas de pivô, permitindo que o SQL recupere e processe dados mais rapidamente. Sempre indexe as colunas frequentemente usadas na cláusula WHERE ou nas colunas que você está agrupando para reduzir o tempo de varredura.

• Avoid Nested Pivots:Empilhar várias operações de pivô em uma consulta pode ser difícil de ler e mais lento para executar. Simplifique dividindo a consulta em partes ou usando uma tabela temporária.

• Limitar Colunas e Linhas no Pivô:Apenas as colunas de pivô são necessárias para a análise, uma vez que pivotar muitas colunas pode ser intensivo em recursos e criar tabelas grandes.

Avoiding common pitfalls

Os seguintes são os erros comuns que você pode encontrar em consultas de pivô e como evitá-los.

• Escaneamentos Completos de Tabela Desnecessários: Consultas de pivô podem desencadear escaneamentos completos de tabela, especialmente se não houver índices relevantes disponíveis. Evite escaneamentos completos de tabela indexando colunas-chave e filtrando dados antes de aplicar o pivô.

• Utilizando SQL Dinâmico para Pivoteamento Frequente: O uso de SQL dinâmico pode diminuir o desempenho devido à recompilação da consulta. Para evitar esse problema, armazene em cache ou limite pivôs dinâmicos para cenários específicos e considere lidar com colunas dinâmicas na camada de aplicação, quando possível.

• Agregando em Conjuntos de Dados Grandes Sem Pré-filtragem: Funções de agregação como SUM ou COUNT em conjuntos de dados grandes podem diminuir o desempenho do banco de dados. Em vez de pivoteamento do conjunto de dados inteiro, filtre os dados primeiro usando uma cláusula WHERE.

• Valores Nulos em Colunas Pivoteadas: Operações de pivotagem frequentemente produzem valores NULL quando não há dados para uma coluna específica. Isso pode retardar as consultas e dificultar a interpretação dos resultados. Para evitar esse problema, use funções como COALESCE para substituir os valores NULL por um padrão.

• Testando Apenas com Dados de Amostra: As consultas de pivoteamento podem se comportar de maneira diferente com conjuntos de dados grandes devido ao aumento da demanda de memória e processamento. Sempre teste as consultas de pivoteamento em dados reais ou amostras representativas para avaliar com precisão os impactos de desempenho.

Tente nossa trilha de carreira SQL Server Developer, que abrange desde transações e tratamento de erros até a melhoria do desempenho de consultas.

Implementações Específicas de Banco de Dados

As operações de pivô diferem significativamente entre bancos de dados como SQL Server, MySQL e Oracle. Cada um desses bancos de dados possui sintaxe e limitações específicas. Vou abordar exemplos de pivotamento de dados nos diferentes bancos de dados e seus principais recursos.

SQL Server

O SQL Server fornece um operador PIVOT integrado, que é direto ao pivotar linhas para colunas. O operador PIVOT é fácil de usar e se integra às poderosas funções de agregação do SQL Server. As principais características do pivô no SQL incluem:

• Suporte direto para PIVOT e UNPIVOT: O operador PIVOT do SQL Server permite rápida transformação de linhas para colunas. O operador UNPIVOT também pode reverter esse processo.

• Opções de Agregação: O operador PIVOT permite várias funções de agregação, como SUM, COUNT e AVG.

A limitação do operador PIVOT no SQL Server é que exige que os valores da coluna a serem pivotados sejam conhecidos previamente, tornando-o menos flexível para dados que mudam dinamicamente.

No exemplo abaixo, o operador PIVOT converte os valores de Product em colunas e agrega Sales usando o operador SUM.

-- Selecionar Data e colunas pivotadas para cada produto
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Subconsulta para buscar as colunas Data, Produto e Vendas
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Agregar Vendas por Produto, pivotando os valores do produto para colunas
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Eu recomendo fazer o curso Introdução ao SQL Server da DataCamp para dominar os conceitos básicos do SQL Server para análise de dados.

MySQL

O MySQL não possui suporte nativo para o operador PIVOT. No entanto, você pode usar a instrução CASE para pivotar manualmente linhas em colunas e combinar outras funções agregadas como SUM, AVG e COUNT. Embora esse método seja flexível, pode se tornar complexo se você tiver muitas colunas para pivotar.

A consulta abaixo alcança a mesma saída que o exemplo PIVOT do SQL Server, condicionalmente agregando vendas para cada produto usando a declaração CASE.

-- Selecionar Data e colunas pivotadas para cada produto
SELECT 
    Date,
    -- Usar CASE para criar uma coluna para vendas de Laptop e 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

O Oracle suporta o operador PIVOT, que permite a transformação direta de linhas em colunas. Assim como no SQL Server, você precisará especificar explicitamente as colunas para a transformação.

No exemplo abaixo, o operador PIVOT converte os valores de ProductName em colunas e agrega SalesAmount usando o operador SUM.

SELECT *
FROM (
    -- Seleção de dados de origem
    SELECT SaleDate, ProductName, SaleAmount FROM SalesData
)
PIVOT (
    -- Agregar Vendas por Produto, criando colunas pivotadas
    SUM(SaleAmount)
    FOR ProductName IN ('Laptop' AS Laptop, 'Mouse' AS Mouse)
);

Exemplo de transformação de saída usando o operador PIVOT do SQL no Oracle. Imagem por Autor.

Técnicas avançadas para transformar linhas em colunas no SQL

Técnicas avançadas para transformar linhas em colunas são úteis quando você precisa de flexibilidade no tratamento de dados complexos. Técnicas dinâmicas e o tratamento de várias colunas simultaneamente permitem transformar dados em cenários onde a pivotação estática é limitada. Vamos explorar esses dois métodos em detalhes.

Pivôs dinâmicos

Pivôs dinâmicos permitem criar consultas de pivô que se adaptam automaticamente a mudanças nos dados. Essa técnica é particularmente útil quando você tem colunas que mudam com frequência, como nomes ou categorias de produtos, e deseja que sua consulta inclua novas entradas automaticamente sem atualizá-la manualmente.

Suponha que temos uma tabela SalesData e podemos criar um pivô dinâmico que se ajusta se novos produtos forem adicionados. Na consulta abaixo, @columns constrói dinamicamente a lista de colunas pivotadas, e sp_executesql executa o SQL gerado.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
-- Passo 1: Gere uma lista de produtos distintos para pivotar
SELECT @columns = STRING_AGG(QUOTENAME(Product), ', ') 
FROM (SELECT DISTINCT Product FROM SalesData) AS products;
-- Passo 2: Construa a 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;';
-- Passo 3: Execute o SQL dinâmico
EXEC sp_executesql @sql;

Manipulando múltiplas colunas

Em cenários onde você precisa pivotar múltiplas colunas simultaneamente, você usará o operador PIVOT e técnicas de agregação adicionais para criar múltiplas colunas na mesma consulta.

No exemplo abaixo, eu pivotei as colunas Sales e Quantity por Product.

-- Pivotar Vendas e Quantidade para Laptop e Mouse por Data
SELECT 
    p1.Date, 
    p1.[Laptop] AS Laptop_Sales, 
    p2.[Laptop] AS Laptop_Quantity, 
    p1.[Mouse] AS Mouse_Sales, 
    p2.[Mouse] AS Mouse_Quantity
FROM 
    (
        -- Pivotar para Vendas
        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
    (
        -- Pivotar para Quantidade
        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;

Exemplo de transformação de saída de múltiplas colunas usando o operador PIVOT do SQL. Imagem do Autor.

Pivoteando múltiplas colunas permite relatórios mais detalhados ao pivotar múltiplos atributos por item, possibilitando insights mais ricos. No entanto, a sintaxe pode ser complexa, especialmente se muitas colunas existirem. A codificação fixa pode ser necessária, a menos que combinada com técnicas de pivot dinâmico, o que adiciona mais complexidade.

Conclusão

Pivoteando linhas para colunas é uma técnica de SQL que vale a pena aprender. Eu vi técnicas de pivot SQL sendo usadas para criar uma tabela de retenção de coorte, onde você pode acompanhar a retenção de usuários ao longo do tempo. Eu também vi técnicas de pivot SQL sendo usadas ao analisar dados de pesquisas, onde cada linha representa um respondente, e cada pergunta pode ser pivotada em sua coluna.

Nosso Curso de Relatórios em SQL é uma ótima opção se você deseja aprender mais sobre como resumir e preparar dados para apresentação e/ou criação de painéis. Nossos Analista de Dados Associado em SQL e Engenheiro de Dados Associado em SQL trilhas de carreira são outra excelente ideia, e eles agregam muito a qualquer currículo, então inscreva-se hoje.