Linhas dinâmicas SQL para colunas: Um guia abrangente

O que significa dinamizar linhas para colunas no SQL?

A dinamização 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álise de dados, permitindo uma visualização de dados mais estruturada e compacta. A dinamização de linhas para colunas também permite que os usuários analisem e resumam os dados de forma a destacar os principais insights com mais clareza.

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

Ao dinamizar essa tabela, posso reestruturá-la para mostrar cada produto como uma coluna, com dados de vendas para cada data em sua coluna correspondente. Observe também que ocorre uma agregação.

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

Noções básicas sobre linhas dinâmicas SQL para colunas

A operação de pivô do SQL transforma os dados, transformando os valores das linhas em colunas. A seguir, você verá a sintaxe e a estrutura básicas do pivô SQL com as seguintes partes:

• SELECIONAR: O comando SELECT faz referência às colunas a serem retornadas na tabela dinâmica SQL.

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

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

-- Select static columns and pivoted columns
SELECT <static columns>, [pivoted columns]
FROM
    (
        -- Subquery defining source data for pivot
        <subquery that defines data>
    ) AS source
PIVOT
(
    -- Aggregate function applied to value column, creating new columns
    <aggregation function>(<value column>)
    FOR <column to pivot> IN ([list of pivoted columns])
) AS pivot_table;

Vejamos o exemplo passo a passo a seguir para demonstrar como você pode dinamizar linhas para colunas no SQL. Considere a tabela SalesData abaixo.

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

Quero dinamizar esses dados para comparar as vendas diárias de cada produto. Começarei selecionando a subconsulta que estruturará o operador PIVOT.

-- Subquery defining source data for pivot
SELECT Date, Product, Sales
FROM SalesData;

Agora, usarei o operador PIVOT para converter os valores de Product em colunas e agregarei Sales usando o operador SUM.

-- Select Date and pivoted columns for each product
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Subquery to fetch Date, Product, and Sales columns
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Aggregate Sales by Product, pivoting product values to columns
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Exemplo de transformação de saída usando linhas de pivô SQL para colunas. Imagem do autor.

Embora a dinamização de dados simplifique o resumo dos dados, essa técnica tem possíveis problemas. A seguir, você encontrará os possíveis desafios com o pivô SQL e como resolvê-los.

• Nomes de colunas dinâmicas: Quando os valores a serem dinamizados (por exemplo, tipos de produtos) são desconhecidos, a codificação de nomes de colunas não funcionará. Alguns bancos de dados, como o SQL Server, suportam SQL dinâmico com procedimentos armazenados para evitar esse problema, enquanto outros exigem que você lide com isso na camada do aplicativo.

• Lidando com valores NULL: Quando não há dados para uma coluna dinâmica específica, o resultado pode incluir NULL. Você pode usar COALESCE para substituir os valores de NULL por zero ou outro espaço reservado.

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

Linhas dinâmicas SQL para colunas: Exemplos e casos de uso

Diferentes métodos são usados para dinamizar dados no SQL, dependendo do banco de dados usado ou de outros requisitos. Embora o operador PIVOT seja comumente usado no SQL Server, outras técnicas, como as instruções CASE, permitem transformações semelhantes no banco de dados sem o suporte direto do PIVOT. Abordarei os dois métodos comuns de dinamização de dados no SQL e falarei sobre os prós e os contras.

Usando o operador PIVOT

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

Considere a seguinte tabela chamada sales_data.

Exemplo de tabela de pedidos para transformar usando o operador PIVOT. Imagem do autor.

Usarei o operador PIVOT para agregar os dados de modo que o total de cada ano sales_revenue seja mostrado em colunas.

-- Use PIVOT to aggregate sales revenue by year
SELECT *
FROM (
    -- Select the relevant columns from the source table
    SELECT sale_year, sales_revenue
    FROM sales_data
) AS src
PIVOT (
    -- Aggregate sales revenue for each year
    SUM(sales_revenue)
    -- Create columns for each year
    FOR sale_year IN ([2020], [2021], [2022], [2023])
) AS piv;

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

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

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

• Limitações: Nem todos os bancos de dados são compatíveis com o operador PIVOT. Isso exige que você especifique as colunas com antecedência, e a dinamização dinâmica exige uma complexidade adicional.

Pivotamento manual com instruções CASE

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

Por exemplo, podemos dinamizar manualmente os dados na mesma tabela sales_data com as instruções CASE.

-- Aggregate sales revenue by year using CASE statements
SELECT 
    -- Calculate total sales revenue for each year
    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 instrução SQL CASE. Imagem do 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 longas se houver muitas colunas a serem dinamizadas. Devido às várias verificações condicionais, o método é um pouco mais lento do que o operador PIVOT.

Considerações sobre o desempenho ao dinamizar linhas para colunas

A dinamização de linhas para colunas no SQL pode ter implicações no desempenho, especialmente quando você trabalha com grandes conjuntos de dados. Aqui estão algumas dicas e práticas recomendadas para ajudar você a escrever consultas dinâmicas eficientes, otimizar seu desempenho e evitar armadilhas comuns.

Práticas recomendadas

A seguir, você encontrará as práticas recomendadas para otimizar suas consultas e melhorar o desempenho.

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

• Evite pivôs aninhados: O empilhamento de várias operações de pivô em uma consulta pode ser difícil de ler e mais lento de executar. Simplifique dividindo a consulta em partes ou usando uma tabela temporária.

• Limitar colunas e linhas na dinamização: Somente colunas dinâmicas são necessárias para a análise, pois a dinamização de muitas colunas pode exigir muitos recursos e criar tabelas grandes.

Como evitar armadilhas comuns

Veja a seguir os erros comuns que você pode encontrar em consultas dinâmicas e como evitá-los.

• Varreduras desnecessárias da mesa inteira: As consultas dinâmicas podem acionar varreduras completas da tabela, especialmente se não houver índices relevantes disponíveis. Evite varreduras completas da tabela indexando colunas-chave e filtrando dados antes de aplicar o pivô.

• Uso de SQL dinâmico para pivotagem frequente: O uso do SQL dinâmico pode reduzir o desempenho devido à recompilação da consulta. Para evitar esse problema, armazene em cache ou limite os pivôs dinâmicos a cenários específicos e considere o manuseio de colunas dinâmicas na camada do aplicativo quando possível.

• Agregação em grandes conjuntos de dados sem pré-filtragem: Funções de agregação como SUM ou COUNT em grandes conjuntos de dados podem reduzir o desempenho do banco de dados. Em vez de dinamizar todo o conjunto de dados, filtre os dados primeiro usando uma cláusula WHERE.

• Valores NULL em colunas dinâmicas: As operações dinâmicas geralmente produzem valores NULL quando não há dados para uma coluna específica. Isso pode tornar as consultas mais lentas e dificultar a interpretação dos resultados. Para evitar esse problema, use funções como COALESCE para substituir os valores de NULL por um padrão.

• Testes apenas com dados de amostra: As consultas dinâmicas podem se comportar de forma diferente com grandes conjuntos de dados devido ao aumento das demandas de memória e processamento. Sempre teste as consultas dinâmicas em amostras de dados reais ou representativas para avaliar com precisão os impactos no desempenho.

Experimente nosso curso de carreira de Desenvolvedor do SQL Server, que abrange tudo, desde transações e tratamento de erros até o aprimoramento do desempenho de consultas.

Implementações específicas de banco de dados

As operações de dinamização diferem significativamente entre bancos de dados como SQL Server, MySQL e Oracle. Cada um desses bancos de dados tem sintaxe e limitações específicas. Abordarei exemplos de dados dinâmicos nos diferentes bancos de dados e seus principais recursos.

SQL Server

O SQL Server fornece um operador incorporado PIVOT, que é simples quando você dinamiza linhas para colunas. O operador PIVOT é fácil de usar e se integra às poderosas funções de agregação do SQL Server. Os principais recursos da dinamização no SQL incluem o seguinte:

• Suporte direto para PIVOT e UNPIVOT: O operador PIVOT do SQL Server permite a transformação rápida de linha em coluna. O operador do 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 ele exige que os valores da coluna a ser dinamizada sejam conhecidos antecipadamente, o que o torna menos flexível para alterar os dados dinamicamente.

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

-- Select Date and pivoted columns for each product
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Subquery to fetch Date, Product, and Sales columns
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Aggregate Sales by Product, pivoting product values to columns
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Recomendo que você faça 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 tem suporte nativo para o operador PIVOT. No entanto, você pode usar a instrução CASE para dinamizar manualmente linhas em colunas e combinar outras funções de agregação como SUM, AVG e COUNT. Embora esse método seja flexível, ele pode se tornar complexo se você tiver muitas colunas para dinamizar.

A consulta abaixo obtém o mesmo resultado que o exemplo do SQL Server PIVOT, agregando condicionalmente as vendas de cada produto usando a instrução CASE.

-- Select Date and pivoted columns for each product
SELECT 
    Date,
    -- Use CASE to create a column for Laptop and Mouse sales
    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 oferece suporte ao operador PIVOT, que permite a transformação direta de linhas em colunas. Assim como no SQL Server, você precisará especificar explicitamente as colunas para transformação.

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

SELECT *
FROM (
    -- Source data selection
    SELECT SaleDate, ProductName, SaleAmount FROM SalesData
)
PIVOT (
    -- Aggregate Sales by Product, creating pivoted columns
    SUM(SaleAmount)
    FOR ProductName IN ('Laptop' AS Laptop, 'Mouse' AS Mouse)
);

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

Técnicas avançadas de dinamização de linhas para colunas no SQL

Técnicas avançadas para dinamizar linhas em colunas são úteis quando você precisa de flexibilidade para lidar com dados complexos. As técnicas dinâmicas e a manipulação de várias colunas simultaneamente permitem que você transforme dados em cenários em que a dinamização estática é limitada. Vamos explorar esses dois métodos em detalhes.

Pivôs dinâmicos

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

Suponhamos que você tenha uma tabela SalesData e possa criar um pivô dinâmico que se ajusta se novos produtos forem adicionados. Na consulta abaixo, o site @columns cria dinamicamente a lista de colunas dinamizadas e o site sp_executesql executa o SQL gerado.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
-- Step 1: Generate a list of distinct products to pivot
SELECT @columns = STRING_AGG(QUOTENAME(Product), ', ') 
FROM (SELECT DISTINCT Product FROM SalesData) AS products;
-- Step 2: Build the dynamic SQL query
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;';
-- Step 3: Execute the dynamic SQL
EXEC sp_executesql @sql;

Manipulação de várias colunas

Em cenários em que você precisa dinamizar várias colunas simultaneamente, você usará o operador PIVOT e técnicas de agregação adicionais para criar várias colunas na mesma consulta. 

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

-- Pivot Sales and Quantity for Laptop and Mouse by Date
SELECT 
    p1.Date, 
    p1.[Laptop] AS Laptop_Sales, 
    p2.[Laptop] AS Laptop_Quantity, 
    p1.[Mouse] AS Mouse_Sales, 
    p2.[Mouse] AS Mouse_Quantity
FROM 
    (
        -- Pivot for Sales
        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
    (
        -- Pivot for Quantity
        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 várias colunas usando o operador SQL PIVOT. Imagem do autor.

A dinamização de várias colunas permite relatórios mais detalhados ao dinamizar vários atributos por item, possibilitando insights mais ricos. No entanto, a sintaxe pode ser complexa, especialmente se houver muitas colunas. A codificação pode ser necessária, a menos que seja combinada com técnicas de pivô dinâmico, o que aumenta a complexidade.

Conclusão

A rotação de linhas para colunas é uma técnica de SQL que vale a pena aprender. Já vi técnicas de pivô SQL usadas para criar uma tabela de retenção de coorte, na qual você pode acompanhar a retenção de usuários ao longo do tempo. Também vi técnicas de pivô SQL usadas ao analisar dados de pesquisa, em que cada linha representa um respondente, e cada pergunta pode ser pivotada em sua coluna. 

Nosso curso Reporting in SQL é uma ótima opção se você quiser saber mais sobre como resumir e preparar dados para apresentação e/ou criação de painéis. Nossas carreiras de Analista de Dados Associado em SQL e Engenheiro de Dados Associado em SQL são outra excelente ideia e acrescentam muito a qualquer currículo, portanto, inscreva-se hoje mesmo.