0Pricing
Coding Interview Prep · Aula

PIVOTs dinâmicos com colunas desconhecidas

Gerando colunas de PIVOT quando as categorias não são conhecidas antecipadamente.

PIVOTs dinâmicos com colunas desconhecidas é uma aula grátis de Coding Interview Prep no CoddyKit. Esta é a aula 4 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de Coding Interview Prep, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de Coding Interview Prep inclui 4 aulas no total.

A questão difícil sobre pivotagem

Toda pivotagem estática, seja uma agregação com CASE, o PIVOT do SQL Server ou o crosstab do PostgreSQL, compartilha uma limitação: você precisa listar as colunas de saída ao escrever a consulta.

Mas e se as categorias forem desconhecidas, como nomes de produtos que mudam semanalmente ou uma coluna para cada mês ativo? Isso é uma pivotagem dinâmica, e é uma questão de entrevista de nível sênior porque o SQL puro não pode retornar um resultado cuja lista de colunas seja determinada em tempo de execução.

Por que o SQL sozinho não consegue fazer isso

O SQL é estaticamente tipado no nível do conjunto de resultados: o planejador precisa conhecer as colunas e seus tipos antes da execução. Uma única consulta não pode dizer crie uma coluna para cada valor que você encontrar.

Assim, a técnica universal é gerar o texto SQL em duas etapas: primeiro consultar as categorias distintas, depois criar uma cadeia de caracteres de consulta de pivotagem a partir delas e executar essa cadeia.

Etapa 1: coletar as categorias

A primeira etapa é uma consulta normal que lista os valores distintos que se tornarão colunas. Normalmente, você os ordena para obter um layout estável das colunas.

Esse resultado alimenta a etapa de criação da cadeia de caracteres. Em um sistema real, você executa essa consulta, captura as linhas e monta a próxima consulta a partir delas.

SELECT DISTINCT quarter
FROM sales
ORDER BY quarter;
-- e.g. Q1, Q2, Q3, Q4

Etapa 2: criar a lista de colunas

Em seguida, transforme esses valores em uma lista separada por vírgulas de expressões CASE (ou nomes entre colchetes para PIVOT). Os bancos de dados fornecem funções de agregação de cadeias de caracteres para fazer isso no próprio SQL.

No PostgreSQL, essa função é string_agg; no MySQL, GROUP_CONCAT; no SQL Server, STRING_AGG ou a técnica mais antiga de FOR XML PATH.

-- Postgres: build the SELECT-list fragment
SELECT string_agg(
  format('SUM(CASE WHEN quarter = %L THEN amount END) AS %I',
         quarter, quarter),
  ', '
)
FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;

Etapa 3: montar e executar

Concatene o fragmento gerado em uma cadeia de caracteres de consulta completa e execute-a com execução dinâmica: EXECUTE em PL/pgSQL, sp_executesql no SQL Server ou PREPARE/EXECUTE no MySQL.

Esse é o coração de uma pivotagem dinâmica: o SQL escreve SQL e depois o executa.

-- SQL Server pattern
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(quarter), ',')
FROM (SELECT DISTINCT quarter FROM sales) q;
SET @sql = N'SELECT region, ' + @cols + '
  FROM (SELECT region, quarter, amount FROM sales) s
  PIVOT (SUM(amount) FOR quarter IN (' + @cols + ')) p;';
EXEC sp_executesql @sql;

Exemplo completo em PostgreSQL

No PostgreSQL, você encapsula as três etapas em um bloco DO ou em uma função. Crie a lista de colunas com string_agg, insira-a na consulta e execute-a com EXECUTE.

Como as colunas do resultado são desconhecidas até o tempo de execução, uma função que retorna esse resultado geralmente usa RETURNS SETOF record ou retorna as linhas como json, que o chamador então expande.

DO $do$
DECLARE
  cols text;
  qry  text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN quarter=%L THEN amount END) AS %I', quarter, quarter), ', ')
  INTO cols
  FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

MySQL com instruções preparadas

O MySQL não tem um operador de pivotagem, portanto as pivotagens dinâmicas criam uma cadeia de caracteres de agregação condicional com GROUP_CONCAT e depois a executam por meio de uma instrução preparada.

GROUP_CONCAT tem um limite de tamanho (group_concat_max_len) que os entrevistadores podem mencionar; aumente-o se você tiver muitas categorias.

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('SUM(CASE WHEN quarter=''', quarter,
         ''' THEN amount END) AS ', QUOTE(quarter))
) INTO @sql FROM sales;
SET @sql = CONCAT('SELECT region, ', @sql,
                  ' FROM sales GROUP BY region');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

O risco de injeção de SQL

Como você está concatenando valores de dados em SQL executável, as pivotagens dinâmicas apresentam um risco de injeção. Se um valor de categoria contiver uma aspa ou texto malicioso, ele poderá corromper ou sequestrar a consulta gerada.

Sempre escape identificadores e literais usando os auxiliares seguros do mecanismo: format('%I', ...) e %L no PostgreSQL, QUOTENAME no SQL Server. Nunca insira valores brutos diretamente na cadeia de caracteres.

-- Safe quoting prevents injection / breakage
-- Postgres: %I identifier, %L literal
format('SUM(CASE WHEN k=%L THEN v END) AS %I', cat, cat)
-- SQL Server: QUOTENAME(cat)

Retornando colunas desconhecidas

Há uma segunda dificuldade: o chamador não pode conhecer antecipadamente a estrutura do resultado. Estratégias comuns aceitas pelos entrevistadores:

  • Retornar as linhas como JSON e deixar a camada da aplicação expandir as chaves.
  • Fazer o procedimento imprimir ou criar a consulta e executá-la em uma segunda etapa.
  • Fazer a pivotagem final no código da aplicação (pandas, ferramenta de BI) depois que as categorias forem conhecidas.

Não há uma maneira simples de retornar colunas arbitrárias a partir de uma única chamada estática.

Exemplo resolvido: pivotagem por produto

Suponha que os produtos entrem e saiam, e que o relatório precise de uma coluna de receita para cada produto atualmente presente em sales. Você não pode codificar a lista diretamente, então deve gerá-la. O PostgreSQL torna isso legível: crie o fragmento CASE com string_agg e delimitação segura, insira-o em uma consulta e depois use EXECUTE.

Explique ao entrevistador o processo: descubra os produtos, formate cada um como uma coluna delimitada, monte a consulta e execute-a. A mesma estrutura se aplica a qualquer mecanismo; apenas os auxiliares mudam.

DO $do$
DECLARE cols text; qry text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN product=%L THEN amount END) AS %I',
           product, product), ', ')
  INTO cols
  FROM (SELECT DISTINCT product FROM sales ORDER BY 1) p;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

Quando evitar pivotagens dinâmicas

Candidatos preparados sabem quando não fazer isso no SQL. O SQL dinâmico é mais difícil de ler, testar, proteger e armazenar em cache. Muitas vezes, a resposta melhor é:

  • Retornar o formato longo a partir do SQL e fazer a pivotagem na aplicação ou na camada de relatórios.
  • Se o conjunto de categorias for pequeno e mudar lentamente, usar uma pivotagem estática e atualizá-la ocasionalmente.

Reserve as pivotagens dinâmicas para conjuntos de categorias realmente abertos e em constante mudança.

Verificação rápida

Teste o motivo central da existência das pivotagens dinâmicas.

Recapitulação

As pivotagens dinâmicas lidam com conjuntos de colunas desconhecidos:

  • As pivotagens estáticas falham porque as colunas do resultado precisam ser fixadas antes da execução.
  • Padrão: consultar as categorias distintas, criar uma cadeia de caracteres SQL de pivotagem e executá-la dinamicamente.
  • Use string_agg/GROUP_CONCAT/STRING_AGG para criar a lista de colunas.
  • Escape os valores (%I/%L, QUOTENAME) para evitar injeção de SQL.
  • Muitas vezes, é mais simples retornar o formato longo e fazer a pivotagem na camada da aplicação.

Perguntas Frequentes

A aula “PIVOTs dinâmicos com colunas desconhecidas” é grátis?

Sim — o texto completo de “PIVOTs dinâmicos com colunas desconhecidas” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de Coding Interview Prep, atualize para CoddyKit PRO. O curso de Coding Interview Prep inclui 4 aulas no total.

O que vou aprender em “PIVOTs dinâmicos com colunas desconhecidas”?

Gerando colunas de PIVOT quando as categorias não são conhecidas antecipadamente. Você pratica Coding Interview Prep com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar Coding Interview Prep?

Nenhuma experiência prévia é necessária. Coding Interview Prep no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 4 de 4.

Quanto tempo leva a aula “PIVOTs dinâmicos com colunas desconhecidas”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de Coding Interview Prep?

Sim. Cada aula de Coding Interview Prep inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

  1. Transformação com agregação condicional
  2. Sintaxe PIVOT de fornecedores e de tabela cruzada
  3. Transformando colunas em linhas
  4. PIVOTs dinâmicos com colunas desconhecidas
← Voltar para Coding Interview Prep