Passo a passo para usar pivoteamento dinâmico
Os bancos de dados bem configurados e definidos armazenam os dados de forma a otimizar o acesso, evitando duplicidade e garantindo a integridade. Porém, em muitas situações isto pode dificultar a apresentação de forma adequada sendo necessário preparar os dados usando vários recursos entre os quais, fazer o pivoteamento.
Se você não precisou ainda usar ou não sabe o que é consiste em transformar cada linha de uma determinada coluna em colunas de uma nova consulta.
Assim, considere uma tabela que armazene as notas bimestrais de um boletim. Uma possível estrutura para esta tabela seria algo assim:
| Coluna | Tipo de dado/Tamanho |
| Disciplina | nvarchar(50) |
| Bimestre | Int |
| Nota | numeric(5,2) |
Uma consulta select nesta tabela com alguns dados traria um resultado parecido com o abaixo:
Porém pode ser que para apresentar estes dados em um relatório seja necessário transformar cada bimestre em uma coluna e agrupar as notas nestas colunas para que fique dessa forma:
Isto pode ser feito de diversas maneiras mas uma das melhores formas se você estiver usando o SQL Server é com o pivoteamento.
Este post mostra um passo a passo para fazer isto de forma dinâmica, baseado nos resultados já existentes em uma tabela para compor as colunas.
Se pivoteamento é totalmente novo para você, leia a referência na documentação oficial aqui. Também é possível entender um pouco mais neste post do site Stackoverflow.
Para facilitar este post está dividido da seguinte maneira:
Passos para resolver o pivoteamento
Os exemplos mais comuns usam valores fixos para as colunas mas, para quem está trabalhando com aplicações SQL Server o que será necessário é transformar os resultados de uma consulta em uma tabela com pivoteamento.
O primerio passo é entender a estrutura de uma consulta deste tipo. Assim, considere a meta consulta abaixo:
SELECT * FROM (
SELECT <colunas>
FROM <tabela>
) AS sourceTable
PIVOT (
<Função de agregação>(<Coluna valores>)
FOR <coluna de pivoteamento> in ( <sequência de valores>)
) AS destTable;
Esta consulta é divida em três partes. A primeira é a parte mais externa que é a responsável por exibir o resutlado obtido com as duas consultas internas. A instrução está sendo executada sobre o resultado nomeado como destTable. Esta parte sempre deve ser usada pois é com ela que os resultados serão exibidos.
A segunda parte faz a consulta na tabela que irá ser usada como fonte dos dados. Devem ser selecionados os dados que serão apresentados. Considerando a tabela de boletim do exemplo dado a primeira parte da consulta fica desta forma:
SELECT * FROM (
SELECT disciplina
,bimestre
,Nota
FROM boletim
) AS t
Por fim o último passo consiste em montar o pivoteamento informando quais serão os valores apresentados e quais as colunas a serem usadas como cabeçalhos. É importante observar que sempre será necessário usar uma função de agregação do SQL Server para o resultado.
Esta consulta fica desta forma usando um modelo básico:
PIVOT (
MAX(Nota)
FOR bimestre IN ( [1], [2], [3], [4] )
) as P;';
Como obter o nome das colunas
O uso esperado é poder usar o pivoteamento com um conjunto variável de valores para a coluna bimestre. Se estiver desenvolvendo uma aplicação para uma escola, por exemplo, durante o ano o número de bimestres irá variar.
Para poder fazer isto a consulta precisa ser montada dinamicamente, ou seja, precisa ser montado o número das colunas para bimestre antes de fazer o pivoteamento. A forma proposta neste post é armazenar os resultados obtidos da coluna bimestre em uma variável com cada valor separado por vírgulas. Isto é conseguido transformando o resultado em uma linha com a cláusula FOR XML PATH.
Esta instrução transforma o resultado em XML mas, como está sendo passado uma sequência vazia de caracteres os valores não receberão as tags mas serão separados por vírgulas.
O uso da função stuff faz com que a posição 1 substitua o caractere encontrado (no exemplo, uma vírgula) por uma sequência vazia:
declare @cols nvarchar(max)
set @cols = stuff((
select distinct ','
+ quotename(Bimestre)
from boletim
for xml path('')
), 1,1, '');
Se inspecionarmos o valor da variável @cols será:
[1],[2],[3],[4]
Gerando e executando a consulta
Com os valores correspondentes às colunas a serem usadas no pivoteamento o resto da consulta pode ser montado da seguinte forma:
declare @query as nvarchar(max)
set @query='SELECT * FROM (
SELECT disciplina
, bimestre
, Nota
FROM boletim
) AS t
PIVOT (
MAX(Nota)
FOR bimestre IN ( ' + @cols + ' )
) as P;';
Por que foi armazenado em uma variável (@query)?
Como é necessário concatenar o conteúdo de uma variável (@cols) para montar a consulta, a melhor forma é montando uma consulta e armazenado em uma variável.
Para visualizar o resultado final da consulta pode ser usada a instrução
print @query
Isto irá produzir a seguinte saída (baseado no exemplo):
SELECT * FROM (
SELECT disciplina
, bimestre
, Nota
FROM boletim
) AS t
PIVOT (
MAX(Nota)
FOR bimestre IN ( [1],[2],[3],[4] )
) as P;
E para executar:
execute(@query)
Com este exemplo você poderá começar a compreender um pouco melhor como fazer pivoteamento dinâmico nas suas consultas.
A instrução completa irá ficar da seguinte forma:
declare @cols nvarchar(max)
set @cols = stuff((
select distinct ','
+ quotename(Bimestre)
from boletim
for xml path('')
), 1,1, '');
-- prepara a consulta
declare @query as nvarchar(max)
set @query='SELECT * FROM (
SELECT disciplina
,bimestre
,Nota
FROM boletim
) AS t
PIVOT (
MAX(Nota)
FOR bimestre IN ( ' + @cols + ' )
) as P;';
execute(@query)