Lição 12.9 · Tempo de leitura: ~7 min

Um pivot transforma os valores de uma coluna em colunas separadas: em vez de linhas "categoria, classificação, quantidade", fica uma linha por categoria com uma coluna para cada classificação, como numa tabela dinâmica de folha de cálculo. Os pivots são a base da maioria dos relatórios cruzados, e "linhas em colunas" é uma tarefa comum de entrevista. Nesta lição, você vai fazer um pivot com agregação condicional, que funciona em qualquer banco de dados, construir um pivot dinâmico no MySQL, ver o operador PIVOT do SQL Server e voltar a transformar colunas em linhas.

Pivot em SQL: Linhas em Colunas

GROUP BY category, rating dá os números certos numa forma pouco prática: 80 linhas de pares categoria–classificação. As pessoas leem mais facilmente uma matriz: categorias na vertical, classificações na horizontal. O MySQL, MariaDB, PostgreSQL e SQLite não têm a palavra-chave PIVOT, nem precisam dela: cada coluna de saída é uma agregação com uma condição. É a agregação condicional da Lição 4.4, aplicada para mudar a forma do resultado.

Os exemplos são executados no banco de exemplo Sakila (MySQL 8.0).

Pivot com Agregação Condicional

Agrupe pelo rótulo das linhas (a categoria) e, para cada futura coluna, conte apenas as linhas dessa classificação:

SELECT
    c.name AS category,
    SUM(CASE WHEN f.rating = 'G' THEN 1 ELSE 0 END) AS g,
    SUM(CASE WHEN f.rating = 'PG' THEN 1 ELSE 0 END) AS pg,
    SUM(CASE WHEN f.rating = 'PG-13' THEN 1 ELSE 0 END) AS pg_13,
    SUM(CASE WHEN f.rating = 'R' THEN 1 ELSE 0 END) AS r,
    SUM(CASE WHEN f.rating = 'NC-17' THEN 1 ELSE 0 END) AS nc_17,
    COUNT(*) AS total
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
GROUP BY c.name
ORDER BY c.name;

Resultado: 16 linhas, uma por categoria, com o número de filmes de cada classificação e o total.

Como funciona:

• GROUP BY c.name define as linhas do pivot.
• Cada CASE transforma um filme em 1 se ele pertence àquela coluna e em 0 se não, por isso o SUM conta os filmes de uma classificação.
• Para fazer pivot de valores em vez de contagens, coloque o valor no CASE: SUM(CASE WHEN ... THEN amount ELSE 0 END).

No MySQL, uma comparação devolve 1 ou 0, por isso SUM(f.rating = 'G') é um equivalente mais curto. O PostgreSQL oferece COUNT(*) FILTER (WHERE f.rating = 'G').

Pivot por Mês

Um relatório de negócio frequente: receita por loja, com uma coluna por mês. Os valores são montantes, e a condição é o mês:

SELECT
    s.store_id,
    SUM(CASE WHEN MONTH(p.payment_date) = 5 THEN p.amount ELSE 0 END) AS may_2005,
    SUM(CASE WHEN MONTH(p.payment_date) = 6 THEN p.amount ELSE 0 END) AS jun_2005,
    SUM(CASE WHEN MONTH(p.payment_date) = 7 THEN p.amount ELSE 0 END) AS jul_2005,
    SUM(CASE WHEN MONTH(p.payment_date) = 8 THEN p.amount ELSE 0 END) AS aug_2005
FROM payment p
JOIN staff s ON s.staff_id = p.staff_id
WHERE p.payment_date >= '2005-01-01'
  AND p.payment_date < '2006-01-01'
GROUP BY s.store_id
ORDER BY s.store_id;

Resultado: 2 linhas; por exemplo, a loja 1 faturou 2621.83 em maio e 14003.54 em julho de 2005.

A condição WHERE limita os dados a um ano, porque o MONTH() sozinho misturaria julho de 2005 com julho de 2006. Use ELSE 0 quando uma célula vazia deve mostrar 0; sem ELSE, o SUM devolve NULL para ela.

Pivot Dinâmico no MySQL

Todas as colunas de uma consulta têm de ser conhecidas antes de ela ser executada. Se a lista de valores do pivot muda (novas classificações, novos meses), a consulta tem de ser gerada. No MySQL, construa a lista de colunas com GROUP_CONCAT e execute o texto como uma instrução preparada:

SET @columns = (
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT('SUM(f.rating = ''', rating, ''') AS `', rating, '`')
        ORDER BY rating)
    FROM film
);

SET @sql = CONCAT(
    'SELECT c.name AS category, ', @columns, '
     FROM film f
     JOIN film_category fc ON fc.film_id = f.film_id
     JOIN category c ON c.category_id = fc.category_id
     GROUP BY c.name
     ORDER BY c.name');

PREPARE pivot_stmt FROM @sql;
EXECUTE pivot_stmt;
DEALLOCATE PREPARE pivot_stmt;

Resultado: as mesmas 16 categorias com uma coluna por classificação; as colunas são construídas a partir dos dados.

Dois cuidados: os valores são inseridos no texto SQL, por isso use isto apenas com valores das suas próprias tabelas, nunca com dados introduzidos pelo utilizador; e listas de colunas muito longas podem exigir um group_concat_max_len maior (1024 caracteres por padrão). Muitas vezes é mais simples devolver o resultado longo do GROUP BY e fazer o pivot na aplicação ou na ferramenta de BI.

O Operador PIVOT no SQL Server e no Oracle

O SQL Server e o Oracle têm uma sintaxe própria. O mesmo relatório no SQL Server:

SELECT category, [G], [PG], [PG-13], [R], [NC-17]
FROM (
    SELECT c.name AS category, f.rating, f.film_id
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    JOIN category c ON c.category_id = fc.category_id
) AS src
PIVOT (
    COUNT(film_id) FOR rating IN ([G], [PG], [PG-13], [R], [NC-17])
) AS pvt;

Os valores em IN (...) continuam a ter de ser listados de antemão. A agregação condicional faz o mesmo e funciona em todo o lado, por isso é a resposta mais segura numa entrevista.

Unpivot: Colunas de Volta para Linhas

A tarefa inversa surge quando os dados chegam como uma folha de cálculo: uma coluna por mês. Faça o unpivot com um SELECT por coluna, unidos com UNION ALL:

WITH store_months AS (
    SELECT
        s.store_id,
        SUM(CASE WHEN MONTH(p.payment_date) = 7 THEN p.amount ELSE 0 END) AS jul_2005,
        SUM(CASE WHEN MONTH(p.payment_date) = 8 THEN p.amount ELSE 0 END) AS aug_2005
    FROM payment p
    JOIN staff s ON s.staff_id = p.staff_id
    WHERE p.payment_date >= '2005-07-01'
      AND p.payment_date < '2005-09-01'
    GROUP BY s.store_id
)
SELECT store_id, '2005-07' AS month, jul_2005 AS amount FROM store_months
UNION ALL
SELECT store_id, '2005-08', aug_2005 FROM store_months
ORDER BY store_id, month;

Resultado: 4 linhas, uma por loja e mês, por exemplo loja 1 – 2005-07 – 14003.54.

O SQL Server e o Oracle também têm o operador UNPIVOT; o PostgreSQL pode usar CROSS JOIN LATERAL (VALUES ...).

Prática

• Distribuição da População (Pivot)
• Distribuição de Filmes por Categoria e Loja
• Receita por Mês e Tipo de Pagamento

Principais conclusões:

• Um pivot transforma os valores de uma coluna em colunas separadas.
• O GROUP BY define as linhas; SUM(CASE WHEN column = value THEN ... ELSE 0 END) cria cada coluna. Funciona em qualquer banco de dados.
• Para montantes, coloque o valor no THEN; para contagens, coloque 1.
• Quando as colunas não são conhecidas de antemão, gere a consulta: no MySQL com GROUP_CONCAT e PREPARE.
• O SQL Server e o Oracle têm PIVOT e UNPIVOT; o UNION ALL faz o unpivot em qualquer lado.

Perguntas Frequentes

Como transformar linhas em colunas no MySQL?

O MySQL não tem o operador PIVOT. Use agregação condicional: GROUP BY pelo rótulo das linhas e um SUM(CASE WHEN pivot_column = 'value' THEN amount ELSE 0 END) AS value por cada coluna de saída.

Como criar um pivot com colunas dinâmicas?

Gere o texto SQL: reúna os valores distintos com GROUP_CONCAT, construa a partir deles a lista de SUM(CASE ...) e depois execute a instrução com PREPARE e EXECUTE. No SQL Server faz-se o mesmo com STRING_AGG e sp_executesql. Use apenas valores confiáveis das suas próprias tabelas.

Qual é a diferença entre PIVOT e agregação condicional?

Dão o mesmo resultado. PIVOT é uma sintaxe mais curta, disponível apenas no SQL Server e no Oracle. A agregação condicional funciona em qualquer banco de dados e permite condições e agregações diferentes para cada coluna.

Como fazer o unpivot de colunas para linhas?

Escreva um SELECT por coluna, cada um a devolver a chave, o nome da coluna como literal e o valor da coluna, e combine-os com UNION ALL. O SQL Server e o Oracle também têm UNPIVOT.

→ Mais tarefas no banco de dados Sakila