Sintaxe PIVOT de fornecedores e de tabela cruzada

Você já conhece a tabela dinâmica portátil com CASE. Mas os entrevistadores também querem saber se você consegue usar operadores de tabela dinâmica específicos de cada fornecedor quando eles estão disponíveis.

O SQL Server inclui um operador PIVOT dedicado. O PostgreSQL oferece uma função crosstab na extensão tablefunc. Conhecer ambos e suas armadilhas demonstra experiência prática.

O PIVOT do SQL Server recebe três elementos:

• Uma agregação sobre a coluna de valores.
• Uma cláusula FOR que nomeia a coluna cujos valores se tornarão novas colunas.
• Uma lista IN dos valores literais que serão transformados em colunas.

Ele precisa ser aplicado a uma tabela derivada que exponha exatamente a chave, a coluna de expansão e o valor, sem nada mais.

Uma armadilha sutil do PIVOT que os entrevistadores testam é que o agrupamento é implícito. O SQL Server agrupa por todas as colunas da fonte que não correspondem à coluna agregada nem à coluna FOR.

Portanto, se a sua tabela derivada incluir acidentalmente uma coluna extra, como order_id, a tabela dinâmica também fará o agrupamento por ela, e você obterá muito mais linhas do que o esperado. Sempre reduza a consulta interna a apenas chave, expansão e valor.

No SQL Server, os nomes das colunas transformadas em tabela dinâmica são os valores literais dos dados, envolvidos em colchetes. Se um valor começar com um dígito ou contiver espaços, os colchetes serão obrigatórios.

Você os seleciona usando o mesmo nome entre colchetes no SELECT externo. É também por isso que PIVOT não consegue lidar com valores desconhecidos sem SQL dinâmico: a lista IN é fixada no código.

O PostgreSQL não tem a palavra-chave PIVOT. Em vez disso, a extensão tablefunc fornece crosstab, uma função que recebe uma string SQL e remodela sua saída.

Você precisa habilitar a extensão primeiro. crosstab espera que a consulta de origem retorne exatamente três colunas: identificador da linha, categoria e valor, nessa ordem.

A parte mais propensa a erros do crosstab é a lista de definição de colunas AS ct(...) no final. Você precisa declarar por conta própria os nomes e tipos das colunas de saída, e eles devem corresponder ao número e à ordem das categorias.

Se uma categoria estiver ausente em uma linha, a tabela cruzada a preencherá conforme a posição, o que pode desalinhá-la, a menos que você use a forma com dois argumentos apresentada abaixo.

Para evitar desalinhamentos quando algumas linhas não tiverem determinadas categorias, use a forma com dois argumentos. A segunda consulta retorna a lista completa e ordenada de valores de categoria, para que a tabela cruzada saiba exatamente em qual coluna cada valor deve ficar.

Essa é a forma robusta esperada pelos entrevistadores quando as categorias são esparsas.

Se o entrevistador perguntar sobre MySQL, a resposta é direta: MySQL não tem PIVOT nem tabela cruzada. A única opção é usar agregação condicional com CASE (ou a forma abreviada SUM(... ) + IF()).

É exatamente por isso que o padrão portátil com CASE é tão valorizado: ele é a alternativa básica que funciona em todos os mecanismos.

Uma solicitação de relatório: uma linha por região, com uma coluna contando os pedidos em cada status. No SQL Server, forneça ao PIVOT uma tabela derivada enxuta usando COUNT.

Como você conta a própria coluna de status, toda linha de status não NULL em um grupo é contabilizada. O SELECT externo lista cada status como uma coluna entre colchetes. Essa é a alternativa concisa para escrever três expressões COUNT(CASE ...).

PIVOT e a tabela cruzada compartilham a mesma limitação básica da agregação condicional: as colunas de saída precisam ser conhecidas quando você escreve a consulta.

• SQL Server: a lista IN é literal.
• Tabela cruzada do PostgreSQL: a lista de definição de colunas é literal.

Nenhuma das duas consegue descobrir categorias em tempo de execução. Isso requer a construção dinâmica da string SQL.

Uma boa resposta em uma entrevista compara as opções com honestidade:

• Agregação com CASE: portável, legível e funciona em todos os mecanismos. É a escolha padrão.
• PIVOT do SQL Server: conciso para muitas colunas, mas o agrupamento implícito surpreende algumas pessoas.
• Tabela cruzada do PostgreSQL: poderosa, mas verbosa; requer uma extensão e uma lista de definição de colunas.

Quando estiver em dúvida, prefira a agregação condicional e mencione os operadores dos fornecedores como alternativas.

Esclareça o comportamento do PIVOT do SQL Server que os entrevistadores costumam investigar.

Sintaxe de PIVOT dos fornecedores em uma tela:

• SQL Server: PIVOT (SUM(x) FOR col IN ([a],[b])), com um GROUP BY implícito sobre as colunas restantes.
• PostgreSQL: crosstab() de tablefunc, exigindo uma lista de definição de colunas; use a forma com dois argumentos para dados esparsos.
• MySQL: nenhum dos dois existe; use CASE.
• Os três exigem que as colunas sejam conhecidas no momento em que a consulta é escrita.