Pular para o conteúdo principal

Pivot dinâmico com SQL Server

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:

Boletim - Consulta linear

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:

Boletim - Consulta com pivoteamento

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:

  1. Passos para resolver o pivoteamento
  2. Como obter o nome das colunas
  3. Gerando e executando a consulta

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)

  

Postagens mais visitadas deste blog

Como gerar scripts para exportar dados no SQL Server 2008

Uma das tarefas mais comuns no trabalho com desenvolvimento de software que consome dados em bancos como o SQL Server 2008 é a necessidade de em algum momento precisarmos exportar os dados de um banco para outro. Quer seja para realizar testes ou fazer simulações existem várias maneiras de se fazer isto. Neste post eu quero demonstrar um recurso do SQL Server Management Studio (SSMS) que permite realizar esta tarefa rapidamente.Para os que estão acostumados a usar esta ferramenta, já devem saber que é possível gerar scripts para o schema e também transferir os dados entre dois bancos distintos. Isto pode ser feito se o SSMS puder conectar-se com as duas bases, de origem e destino. No exemplo que vou dar, o objetivo é gerar o script apenas para uma tabela do banco de dados de exemplo da Microsoft – Northwind.1. Iniciando o assistenteO assistente deve ser iniciado clicando com o botão direito do mouse sobre o banco onde se encontra a tabela a qual iremos gerar o script. Deve se clicar n…

Como remover espaços e quebra de linha de documentos XML

A dica de hoje (após um longo e tenebroso inverno, que a propósito continua aqui no Paraná) nasceu de um incêndio que precisou ser apagado nos projetos da vida com prazo de entrega pra “ontem”… mas, vamos a parte divertida.No Framework .NET a geração de documentos XML é facilitada de várias formas. Vamos partir do código abaixo para gerar um documento simples usando apenas System.Xml.var xmlDoc = new XmlDocument();
XmlElement raiz = xmlDoc.CreateElement("artigo");
XmlElement Id = xmlDoc.CreateElement("id");
Id.InnerText = "08072011";
XmlElement Titulo = xmlDoc.CreateElement("titulo");
Titulo.InnerText = "Como remover espaços e quebra de linha de documentos XML";
XmlElement Vazio = xmlDoc.CreateElement("vazio");
Vazio.InnerText = String.Empty;
raiz.AppendChild(Id);
raiz.AppendChild(Titulo);
raiz.AppendChild(Vazio);
xmlDoc.AppendChild(raiz);
xmlDoc.Save(@"d:\teste.xml");

O código acima gera o arquivo “teste.xml” que ao ser visuali…