Um ótimo recurso para utilizar com tabelas e principalmente com arquivos importados de outras fontes para o banco de dados é saber utilizar a técnica de realizar pivot. 

A ‘pivotagem’ de uma tabela ou arquivo nada mais é que trocar linha por coluna e coluna por linha. Pode ser muito útil principalmente quando importamos arquivos de excel.

Neste post, irei mostrar como realizar pivot de tabelas com a linguagem SQL. 

PIVOT

Como dito, na sequência abaixo, vamos verificar como podemos rotacionar os dados de um estado para outro no banco de dados. A função de pivot quando executada realiza a operação de forma mais concisa e eficiente. 

Virá sempre na cláusula FROM e age da mesma forma lógica que a cláusula case com diferença que não precisamos utilizar tantas linhas de código além de dispensar o uso da cláusula group by.

Quando utilizamos o pivot, o que ele faz é pegar todos os resultados de uma coluna, horizontalizar e exibir este resultado.

Temos duas formas de realizar o pivot das tabelas sendo uma por função agregada utilizando case e a outra utilizando a função pivot.

Executando pivot com a cláusula case: 

A query ao invés de exibir territoryid e subtotal  como colunas, no formato como conhecemos, pegou os valores de territoryid espalhou como se fossem colunas e agregou pela função SUM os valores da coluna subtotal exibindo conforme o exemplo.

Agora, vamos ver como utilizamos a função PIVOT  APÓS  a cláusula FROM. 

Comparando os dois resultados da operação de pivot, gostaria que reparassem na quantidade de linhas que as duas queries retornam. Veja que o primeiro exemplo retorna 18 linhas apenas e a segunda query retorna 31,465 linhas.

Esta situação ocorre porque a função PIVOT IMPLICITAMENTE realiza uma operação de agregação, ainda que não precisemos declarar o GROUP BY na query. Com isso, todas as colunas desta tabela ‘participam’ da agregação e assim sendo, retornam essa quantidade de linhas, coisa que no primeiro exemplo não ocorre, uma vez que declaramos a coluna no GROUP BY.

E para resolver esse pequeno problema, darei um exemplo de como realizar a operação de PIVOT seguindo a boa prática para este tipo:

Na query acima, utilizamos um recurso já abordado em posts anteriores que se chama tabela derivada criando uma tabela virtual somente com as colunas que desejamos envolver na operação eliminando a grande quantidade de NULLs e linhas retornadas.

Outa forma também considerada uma boa prática para a operação de pivot é utilizando as famosas C.T.Es. Veja.

Como sabemos em alguns casos, os valores nulls podem aparecer na por serem desconhecidos ao banco e para isso, precisamos substituí-los por outros valores para não comprometer a qualidade dos dados.

No exemplo abaixo, vamos fazer isto para a próxima operação de pivot.

NOTA: neste exemplo optei pelo uso do ISNULL mas na prova, optem pelo coalesce.

Algumas limitações no uso de pivot.

A coluna da função de agregação e a coluna que ‘espalhamos’ na função não podem participar do select .
Não é permitido o uso de count(*) na função.
Não é permitido o uso de mais de uma função de agregação, inclusive, não podemos utilizar funções cast e convert.

UNPIVOT

Esta é uma operação inversa, que transforma os dados exibidos em estado de coluna para linha. 

O caso mais comum é quando importamos algum arquivo em formato planilha para o banco de dados.

A operação envolve duas etapas no seu processo:

  1. Transforma as colunas em linhas onde estas passarão a dar o nome de cada linha;
  2. E atribui os valores destas colunas para suas respectivas linhas

Tem a mesma ordem lógica que a função PIVOT  operando também após a cláusula FROM.

Veja o exemplo:

Um outro exemplo para fixar a matéria:

SINTAXES

SINTAXE UNPIVOT

SINTAXE PIVOT

Assim encerro este post sobre um excelente recurso para transformada de resultados e alteração de visualização em consultas.

Estas duas funções são de extrema utilidade quando queremos criar análises mais pontuais comparando o desempenho de algum atributo escolhido

Uma dica para não errar é pensar sempre na coluna que se quer criar a agregação e na coluna que se deseja horizontalizar ou verticalizar!

Link com o script!

Espero que tenham gostado! Saúde!