Uma dúvida recorrente em fóruns de SQL Server é sobre pivô (ou pivot). Embora a definição formal seja “eixo vertical fixo, à volta da qual gira uma peça móvel”, em SQL “pivotar” é muito mais do que transformar linhas em colunas.
Neste artigo o assunto é apresentado passo a passo, detalhando como montar pivô, pivô clássico e também o temido pivô dinâmico.
1. Introdução
1.1. Fóruns
No dia a dia de colaboração em fóruns de SQL Server percebo dúvidas que são recorrentes e uma delas é sobre pivô (ou pivot). O grau de complexidade varia, indo dos mais simples, que se resolve com o operador relacional PIVOT, até os mais complexos, em que é necessário montar o comando SQL em uma variável para então executá-lo através da função sp_executesql.
Há poucos dias respondi a mais um tópico sobre pivotamento, este de resolução complexa. Aliás, a dois, em um curto intervalo de tempo e ambos bem semelhantes. Tanto que a solução que propus para o primeiro depois a adaptei para o segundo caso.
1.2. O que é o pivô?
Quem acompanha esportes como basquete ou futebol de salão conhece termos como ala ou pivô. Mas o contexto deste artigo não é sobre esportes; então não teremos alas. Mas teremos pivôs, só que em outro contexto.
Ao consultar dicionários, a definição mais próxima que encontrei foi “eixo vertical fixo, à volta da qual gira uma peça móvel” (Infopedia). E é isto que ocorre no SQL, quando se executa um pivotamento: como “eixo vertical fixo” definimos geralmente uma coluna e como “peça móvel” definimos um conjunto de valores, dispostos em linhas/coluna, que serão transformados em colunas/linha.
Supondo a seguinte tabela, e tendo a coluna Ano como pivô,
teremos o seguinte resultado após o pivotamento:
Houve então uma rotação ao redor do valor 2018 da coluna Ano, sendo que os valores da coluna Mês tornaram-se títulos das novas colunas e os valores da coluna Qtd tornaram-se os valores da linha 2018.
Considero o pivotamento simples como uma tarefa de apresentação, em que os dados passam a ser exibidos de maneira diferente da que estão armazenados no banco de dados. Entretanto, geralmente há um processo de sumarização antes da realização do pivotamento.
1.3. Tabelas para exemplos
Ao longo do artigo há vários códigos que você pode testar em seu computador, à medida que o lê. Os códigos para criar as tabelas de testes estão definidos no item “Código T-SQL”, ao final do artigo.
2. Pivô estático
Por estático entenda-se que o pivô é realizado em código T-SQL sem o uso de comando SQL dinâmico.
2.1. Operador relacional PIVOT
De acordo com a documentação de PIVOT, ele é um operador relacional. A sintaxe básica é a seguinte:
Passo 1. Para montar o código primeiro começamos por informar o pivô, na cláusula SELECT.
SELECT Ano, ...
Passo 2. A seguir passamos para a cláusula FROM. Na sintaxe de PIVOT consta que deve-se utilizar <consulta que gera os dados>. E porque não direto uma tabela? O motivo é simples: para o operador relacional somente podem ser encaminhadas as colunas que farão parte do resultado. Se tivermos uma tabela com dezenas de colunas e ela for informada diretamente, o resultado é inesperado. Mas no nosso caso temos uma tabela que contém somente colunas que serão utilizadas pelo operador PIVOT. Como sabemos que a coluna que será pivô é a Ano, temos então:
SELECT Ano, ...
from dbo.tbVenda_2018
PIVOT ...
Passo 3. O próximo passo é definir a coluna a ser girada ao redor do pivô; neste exemplo é a Qtd, pois seus valores, originalmente exibidos em uma coluna passarão a ser exibidos em uma linha. Na sintaxe consta a necessidade de definir uma função de agregação, mas no nosso exemplo não há necessidade de agregação, pois há somente uma linha para cada valor de mês/ano. Então escolhemos uma função que sabemos que não irá afetar o resultado; neste exemplo usaremos a função SUM().
SELECT Ano, ...
from dbo.tbVenda_2018
PIVOT (sum (Qtd)
for ...
Passo 4. E então é o momento de definir a coluna que possui os valores que se tornarão a denominação das novas colunas. Pelo que vimos no item 1.2, essa coluna é a Mês.
SELECT Ano, ...
from dbo.tbVenda_2018
PIVOT (sum (Qtd)
for Mês ...
Passo 5. É necessário conhecer de antemão quais são os valores que se tornarão títulos das colunas pivotadas, pois eles devem ser informados tanto na lista de colunas da cláusula SELECT quanto na cláusula PIVOT. A seguir acrescentamos os valores da coluna Mês que se tornarão títulos das novas colunas (que são de 1 a 12) e fechamos a definição de PIVOT.
SELECT Ano, ...
from dbo.tbVenda_2018
PIVOT (sum (Qtd)
for Mês in ([1], [2], [3], [4], [5], [6],
[7], [8], [9], [10], [11], [12])) as P
Passo 6. Retornamos então para a cláusula SELECT, onde acrescentamos a denominação das novas colunas.
Ao final, temos o código completo:
-- código #2.1
SELECT Ano, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]
from dbo.tbVenda_2018
PIVOT (sum (Qtd)
for Mês in ([1], [2], [3], [4], [5], [6],
[7], [8], [9], [10], [11], [12])) as P;
Simples, não?
2.2. Pivô clássico
Antes da versão 2008 do SQL Server não existia o operador relacional PIVOT. Então, o pivotamento era realizado utilizando GROUP BY.
Parece complexo, mas é bem simples depois que se habitua a utilizar. Da mesma forma que no item anterior, vamos seguir passo a passo o processo de construção do código SQL.
Passo 1. Iniciamos por informar o pivô, nas cláusulas SELECT e GROUP BY:
SELECT Ano, ... from ... group by Ano;
Passo 2. A seguir passamos para a cláusula FROM. Na sintaxe consta <consulta que gera os dados>, mas no nosso caso podemos utilizar diretamente a tabela:
SELECT Ano, ...
from dbo.tbVenda_2018
group by Ano;
Passo 3. O passo seguinte é definir a coluna a ser girada ao redor do pivô; neste exemplo é a Qtd, pois seus valores, originalmente exibidos em uma coluna passarão a ser exibidos em uma linha. Na sintaxe consta a necessidade de definir uma função de agregação, mas no nosso exemplo não há necessidade de agregação, pois há somente uma linha para cada valor de mês/ano. Então escolhemos uma função que sabemos que não irá afetar o resultado; neste exemplo usaremos a função SUM().
SELECT Ano,
sum (case when ... then Qtd end) as ...
from dbo.tbVenda_2018
group by Ano;
Passo 4. Próximo passo é definir a coluna que possui os valores que se tornarão a denominação das novas colunas. Pelo que vimos no item 1.2, essa coluna é a Mês.
SELECT Ano,
sum (case when Mês = ... then Qtd end) as ...
from dbo.tbVenda_2018
group by Ano;
Passo 5. É necessário conhecer de antemão quais são os valores que se tornarão títulos das colunas pivotadas. Acrescentamos então os valores da coluna Mês que se tornarão títulos das novas colunas, que são de 1 a 12.
SELECT Ano,
sum (case when Mês = 1 then Qtd end) as ...,
sum (case when Mês = 2 then Qtd end) as ...,
sum (case when Mês = 3 then Qtd end) as ...,
sum (case when Mês = 4 then Qtd end) as ...,
sum (case when Mês = 5 then Qtd end) as ...,
sum (case when Mês = 6 then Qtd end) as ...,
sum (case when Mês = 7 then Qtd end) as ...,
sum (case when Mês = 8 then Qtd end) as ...,
sum (case when Mês = 9 then Qtd end) as ...,
sum (case when Mês = 10 then Qtd end) as ...,
sum (case when Mês = 11 then Qtd end) as ...,
sum (case when Mês = 12 then Qtd end) as ...
from dbo.tbVenda_2018
group by Ano;
Passo 6. E para finalizar acrescentamos a denominação das novas colunas.
Temos então o código completo:
-- código #2.2
SELECT Ano,
sum (case when Mês = 1 then Qtd end) as [1],
sum (case when Mês = 2 then Qtd end) as [2],
sum (case when Mês = 3 then Qtd end) as [3],
sum (case when Mês = 4 then Qtd end) as [4],
sum (case when Mês = 5 then Qtd end) as [5],
sum (case when Mês = 6 then Qtd end) as [6],
sum (case when Mês = 7 then Qtd end) as [7],
sum (case when Mês = 8 then Qtd end) as [8],
sum (case when Mês = 9 then Qtd end) as [9],
sum (case when Mês = 10 then Qtd end) as [10],
sum (case when Mês = 11 then Qtd end) as [11],
sum (case when Mês = 12 then Qtd end) as [12]
from dbo.tbVenda_2018
group by Ano;
Escreve-se mais. Entretanto, é bom conhecer a implementação do pivô clássico, pois será útil mais à frente. Aliás, internamente o que o query optimizer faz é transformar o operador relacional PIVOT em uma construção de pivô clássico. Analise o plano de execução que perceberá isto.
2.3. Pivô girando mais de uma coluna
Às vezes é necessário que o pivô gire mais de uma coluna. Ao analisar a sintaxe do operador relacional PIVOT percebemos que ele permite somente uma coluna; ou seja, nada feito. E é então que entra em ação o pivô clássico, para resolver essa necessidade.
Como exemplo vamos utilizar a tabela que consta no tópico “Pivot Table com mais de uma coluna”. O autor do tópico cita que possui uma tabela de produtos, com 3 colunas de informação, e deseja que duas delas sejam pivotadas.
O objetivo é ter a seguinte tabela, após o pivotamento:
A coluna Produto é o pivô e as colunas a serem pivotadas são a Metros e a Peso. Usando o pivô clássico, temos a seguinte solução:
-- código #2.3
SELECT Produto,
Cat01_Metros= sum (case when Categoria='01'
then Metros else 0 end),
Cat01_Peso= sum (case when Categoria='01'
then Peso else 0 end),
Cat02_Metros= sum (case when Categoria='02'
then Metros else 0 end),
Cat02_Peso= sum (case when Categoria='02'
then Peso else 0 end),
CatRT_Metros= sum (case when Categoria='RT'
then Metros else 0 end),
CatRT_Peso= sum (case when Categoria='RT'
then Peso else 0 end)
from tbProduto
group by Produto;
Como exercício, tente resolver este caso usando o operador relacional PIVOT.
2.4. Relatório anual de vendas
Nos exemplos anteriores houve somente transposição de linhas/coluna em linha/colunas, sem realizar qualquer agregação (soma, média etc). Mas, como consta na sintaxe do operador relacional PIVOT, ele está preparado para realizar uma agregação antes de girar as colunas ao redor do pivô e é isto que faremos agora. Para o exemplo utilizaremos a tabela dbo.tbVenda_Diaria para obter relatório de vendas anuais por loja:
A tabela possui informação da loja e do vendedor, mas para esse relatório necessitamos de somente informações de lojas. É agora onde utilizamos a (<consulta que gera os dados>) para constar somente as colunas necessárias.
A tabela dbo.tbVenda_Diaria possui as colunas Data_Venda, Cod_Loja, Cod_Vendedor e Valor_Venda, mas a informação da coluna Cod_Vendedor não é necessária. Para selecionar somente as colunas úteis, vamos iniciar pela utilização de CTE para construir a consulta:
with Venda_Loja as ( SELECT year (Data_Venda) as Ano, Cod_Loja, Valor_Venda from dbo.tbVenda_Diaria where Data_Venda < '20190101' ) ...
O nosso pivô é o ano; por isso que na CTE Venda_Loja foram suprimidas as informações de dia e mês da venda. A função de agregação utilizada é a SUM() e sabemos que os códigos das lojas são 1, 2 e 3. Seguindo os passos que aprendemos no item 2.1, chegamos ao seguinte código:
-- código #2.4
with Venda_Loja as (
SELECT year (Data_Venda) as Ano, Cod_Loja, Valor_Venda
from dbo.tbVenda_Diaria
where Data_Venda < '20190101')
SELECT Ano, [1] as [Loja 1], [2] as [Loja 2], [3] as [Loja 3]
from Venda_Loja
PIVOT (sum (Valor_Venda)
for Cod_Loja in ([1], [2], [3])) as P;
Temos o seguinte resultado:
As centenas de linhas de vendas diárias tiveram seus valores de vendas sumarizados por ano/loja de forma bem simples.
Mas e se o relatório tivesse que ser emitido por mês e ano? Nesse caso teríamos um pivô composto, isto é, o pivô com mais de uma coluna. Até agora todos os exemplos o pivô tinha somente uma coluna. Sem rodeios, eis o código para o ano de 2017:
-- código #2.5
with Venda_Loja as (
SELECT year (Data_Venda) as Ano,
month (Data_Venda) as Mês,
Cod_Loja, Valor_Venda
from dbo.tbVenda_Diaria
where Data_Venda >= '20170101'
and Data_Venda < '20180101'
)
SELECT Mês, Ano, [1] as [Loja 1], [2] as [Loja 2],
[3] as [Loja 3]
from Venda_Loja
PIVOT (sum (Valor_Venda)
for Cod_Loja in ([1], [2], [3])) as P
order by Ano, Mês;
Na CTE Venda_Loja foram criadas as colunas de mês e ano bem como filtradas as linhas do ano de 2017. No SELECT principal, o pivô.
3. Pivô dinâmico
Nos exemplos do capítulo anterior era conhecido o domínio da coluna de valores; aquela cujos valores se transformarão em novas colunas. Entretanto, há casos em que não se conhece de antemão quais são os valores distintos e nem a quantidade deles. Para resolver essa situação utilizamos comando SQL dinâmico, contendo o código T-SQL completo do pivô. A esta abordagem denominamos de “pivô dinâmico”.
3.1. Relatório anual de vendas com pivô dinâmico
Para facilitar a explicação, vamos reutilizar a tabela dbo.tbVenda_Diaria e o caso que consta no item 2.4, para obter o relatório anual de vendas, por loja. Vamos utilizar o pivô dinâmico para construir código T-SQL que seja independente dos dados.
No item 2.4 os códigos das lojas eram previamente informados. Ao reescrever o código #2.4 sem as informações de códigos de lojas temos o seguinte esboço:
-- código #3.1
with Venda_Loja as (
SELECT year (Data_Venda) as Ano, Cod_Loja, Valor_Venda
from dbo.tbVenda_Diaria
where Data_Venda < '20190101'
)
SELECT Ano, título
from Venda_Loja
PIVOT (sum (Valor_Venda)
for Cod_Loja in (valores)) as P;
Para montar o trecho título da cláusula SELECT temos que obter os códigos das lojas, em ordem crescente, e também realizar a criação do alias de cada valor.
-- código #3.2
-- monta valores de códigos de loja e respectivos aliases
declare @Colunas varchar(500);
set @Colunas='';
SELECT @Colunas += '['
+ cast (Cod_Loja as varchar(5))
+ '] as [Loja '
+ cast (Cod_Loja as varchar(5))
+ '], '
from (SELECT distinct Cod_Loja
from dbo.tbVenda_Diaria
order by Cod_Loja) as T;
set @Colunas= left (@Colunas, datalength(@Colunas)-2);
Ao executar o código acima ocorre erro, sendo retornada a mensagem de erro
A cláusula ORDER BY é inválida em exibições, funções embutidas, tabelas derivadas, subconsultas e expressões de tabela comuns, a menos que TOP ou FOR XML também estejam especificados.
Utilizando então XML e a função STUFF, temos o seguinte código que obtém os códigos de loja em ordem crescente:
-- código #3.3
-- monta valores de códigos de loja e respectivos aliases
declare @Titulos varchar(500);
set @Titulos=
stuff ((SELECT distinct ', ' + T.titulo
from (SELECT '['
+ cast (Cod_Loja as varchar(5))
+ '] as [Loja '
+ cast (Cod_Loja as varchar(5))
+ ']' as titulo
from dbo.tbVenda_Diaria) as T
order by ', ' + T.titulo asc
for xml path('')),
1, 2, '');
SELECT @Titulos;
Ao executar o código acima obtemos o seguinte conteúdo na variável @Titulos:
E código semelhante é utilizado para montar o trecho valores:
-- código #3.4
-- monta valores de códigos de loja
declare @Valores varchar(500);
set @Valores=
stuff ((SELECT distinct ', ' + quotename (Cod_Loja)
from dbo.tbVenda_Diaria as T
order by ', ' + quotename (Cod_Loja) asc
for xml path('')),
1, 2, '');
SELECT @Valores;
Ao executar o código acima obtemos o seguinte conteúdo na variável @Valores:
Ou seja, já temos as partes necessárias para acrescentar ao código #3.1. O código completo do pivô dinâmico fica então assim:
-- código #3.5
-- monta títulos de lojas
declare @Titulos nvarchar(500);
set @Titulos=
stuff ((SELECT distinct ', ' + T.titulo
from (SELECT '['
+ cast (Cod_Loja as varchar(5))
+ '] as [Loja '
+ cast (Cod_Loja as varchar(5))
+ ']' as titulo
from dbo.tbVenda_Diaria) as T
order by ', ' + T.titulo asc
for xml path('')),
1, 2, '');
-- monta valores de códigos de loja
declare @Valores nvarchar(500);
set @Valores=
stuff ((SELECT distinct ', ' + quotename (Cod_Loja)
from dbo.tbVenda_Diaria as T
order by ', ' + quotename (Cod_Loja) asc
for xml path('')),
1, 2, '');
-- monta texto do comando SQL em variável
declare @ComandoSQL nvarchar(2000);
set @ComandoSQL=
N'with Venda_Loja as ('
+ N'SELECT year (Data_Venda) as Ano, Cod_Loja, Valor_Venda '
+ N'from dbo.tbVenda_Diaria '
+ N'where Data_Venda < '''20190101''
+ N') '
+ N'SELECT Ano, ' + @Titulos + ' '
+ N'from Venda_Loja '
+ N'PIVOT (sum (Valor_Venda) '
+ N'for Cod_Loja in (' + @Valores + N')) as P;';
-- executa o comando SQL
--PRINT @ComandoSQL;
EXECUTE sp_executesql @ComandoSQL;
Ao executar o código acima o resultado é o mesmo obtido do código #2.4.
A vantagem no uso de pivô dinâmico é que o código funciona independente dos valores que existam na tabela de vendas diárias.
3.2. Média móvel com pivô dinâmico
“Ano móvel” refere-se a efetuar determinada operação com dados dos 12 meses anteriores ao mês atual. A operação pode ser, por exemplo, calcular o valor médio ou então a somatória dos valores. Independente da operação, a forma de coletar os dados é a mesma.
O próximo código deve calcular o ticket médio de vendas de cada loja, mês a mês no ano corrente, considerando-se o ano móvel. Por exemplo, se o mês corrente for março/2019 então no relatório devem constar colunas de janeiro/2019 a março/2019. Em cada coluna de ser calculada a média dos valores dos 12 meses imediatamente anteriores, para cada loja: para o mês de março/2019 utilizam-se então os dados de março/2018 a fevereiro/2019; para o mês de fevereiro/2019 utilizam-se os dados de fevereiro/2018 a janeiro/2019; e assim em diante.
A apresentação deve ser na seguinte forma:
Para desenvolver este exemplo será utilizada a tabela dbo.tbVenda_Diaria.
Calcular o ticket médio mensal por loja das vendas do ano corrente é bem simples:
-- código #3.6
-- obtém ano atual
declare @Ano_corrente smallint;
set @Ano_corrente= year (current_timestamp);
--
SELECT month (Data_Venda) as [Mês],
year (Data_Venda) as Ano,
Cod_Loja,
avg (Valor_Venda) as [Ticket médio]
from dbo.tbVenda_Diaria
where year (Data_Venda) = @Ano_corrente
group by month (Data_Venda), year (Data_Venda), Cod_Loja;
Entretanto, o que se deve obter é o ticket médio dos 12 meses anteriores. E, além disso, deve-se ficar atento para construir predicados sargable, o que não ocorreu na cláusula WHERE do código anterior.
O primeiro passo é obter relação de meses do ano corrente, até o mês atual (inclusive). Há algumas formas, mas neste caso vamos adotar a CTE recursiva.
-- código #3.7
-- obtém mês e ano atual
declare @Hoje date, @Mes_corrente tinyint, @Ano_corrente smallint;
set @Hoje= cast (current_timestamp as date);
set @Mes_corrente= month (@Hoje);
set @Ano_corrente= year (@Hoje);
with
Meses_emissao as (
SELECT convert (date,
'1/1/' + cast (@Ano_corrente as char(4)),
103) as Data
union all
SELECT dateadd (month, +1, R.Data)
from Meses_emissao as R
where month (R.Data) < @Mes_corrente
)
SELECT Data from Meses_emissao;
A CTE Meses_emissao fornecerá os meses de referência. A execução do código anterior resultou no seguinte conjunto de dados:
Para cada mês de referência deve-se calcular o ticket médio dos 12 meses anteriores. Ainda utilizando CTE, temos então
-- código #3.8
-- obtém mẽs e ano atual
declare @Hoje date, @Mes_corrente tinyint, @Ano_corrente smallint;
set @Hoje= cast (current_timestamp as date);
set @Mes_corrente= month (@Hoje);
set @Ano_corrente= year (@Hoje);
with
Meses_emissao as (
SELECT convert (date,
'1/1/' + cast (@Ano_corrente as char(4)),
103) as Data
union all
SELECT dateadd (month, +1, R.Data)
from Meses_emissao as R
where month (R.Data) < @Mes_corrente
),
Leitura_12meses as (
SELECT month (M.Data) as [Mês],
VD.Cod_Loja, Valor_Venda
from Meses_emissao as M
cross join dbo.tbVenda_Diaria as VD
where VD.Data_Venda >= dateadd (year, -1, M.Data)
and VD.Data_Venda < M.Data
)
SELECT * from Leitura_12meses;
Já temos o conjunto de linhas a tratar e podemos então esboçar o SELECT final, com o pivô.
-- código #3.9
...
with
Meses_emissao as (
...
),
Leitura_12meses as (
...
)
SELECT Cod_Loja, [1], [2], [3]
from Leitura_12meses
pivot (avg (Valor_Venda) for Mês in ([1], [2], [3]));
Entretanto, o número de colunas de mês é variável, o que significa o uso de pivô dinâmico. Do código #3.5 sabemos que temos que primeiro gerar variáveis contendo os títulos das novas colunas e também os valores utilizados para criar as novas colunas. A seguir montar o texto do comando SQL e então executá-lo. No item 3.1 foi mostrado passo a passo mas agora que já conhecemos o caminho das pedras podemos montar diretamente o código de emissão do relatório de médias móveis de vendas, por loja.
-- código #3.10
-- obtém mês e ano atual
declare @Hoje date, @Mes_corrente tinyint, @Ano_corrente smallint;
set @Hoje= cast (current_timestamp as date);
set @Mes_corrente= month (@Hoje);
set @Ano_corrente= year (@Hoje);
-- gera lista com meses do ano, até mês atual
IF Object_Id ('tempDB..#Meses_emissao', 'U') is not null
DROP TABLE #Meses_emissao;
with
Meses_emissao as (
SELECT convert (date,
'1/1/' + cast (@Ano_corrente as char(4)),
103) as Data
union all
SELECT dateadd (month, +1, R.Data)
from Meses_emissao as R
where month (R.Data) < @Mes_corrente
)
SELECT Data
into #Meses_emissao
from Meses_emissao;
-- monta títulos de meses
declare @Titulos nvarchar(500);
set @Titulos=
stuff ((SELECT distinct ', ' + T.titulo
from (SELECT '['
+ convert (char(8), Data, 112)
+ '] as ['
+ left (datename (month, Data), 3)
+ ' '
+ convert (char(4), Data, 112)
+ ']' as titulo
from #Meses_emissao) as T
order by ', ' + T.titulo asc
for xml path('')),
1, 2, '');
-- monta valores de meses
declare @Valores nvarchar(500);
set @Valores=
stuff ((SELECT distinct ', ' + T.valor
from (SELECT '['
+ convert (char(8), Data, 112)
+ ']' as valor
from #Meses_emissao) as T
order by ', ' + T.valor asc
for xml path('')),
1, 2, '');
-- monta texto do comando SQL em variável
declare @ComandoSQL nvarchar(2000);
set @ComandoSQL=
'with ' +
'Leitura_12meses as ( ' +
'SELECT M.Data, VD.Cod_Loja, VD.Valor_Venda ' +
'from #Meses_emissao as M ' +
'cross join dbo.tbVenda_Diaria as VD ' +
'where VD.Data_Venda >= dateadd (year, -1, M.Data) ' +
'and VD.Data_Venda < M.Data ' +
') ' +
'SELECT Cod_Loja, ' + @Titulos + ' ' +
'from Leitura_12meses ' +
'pivot (avg (Valor_Venda) ' +
'for Data in (' + @Valores + ')) as P;';
-- gera o relatório
EXECUTE sp_executesql @ComandoSQL;
--
IF Object_Id ('tempDB..#Meses_emissao', 'U') is not null
DROP TABLE #Meses_emissao;
O resultado da execução do código anterior, considerando o mês de maio de 2019 como mês corrente, é o seguinte:
O resultado é o esperado, mas ao analisar o plano de execução percebe-se elevado número de leituras repetidas na tabela dbo.tbVenda_Diaria. O que você faria para reduzir as leituras repetidas?
4. Conclusão
Procurou-se estruturar este artigo iniciando com modelos simples e aumentando o grau de dificuldade ao longo do artigo. Para dominar o assunto, o principal é conhecer as duas formas de executar o pivô: o operador relacional PIVOT e o pivô clássico (GROUP BY).
Espero que este artigo tenha sido esclarecedor sobre o que é o pivô e as várias formas de utilizá-lo.
5. Referências
5.1. Documentação BOL
5.2. Tópicos de fóruns
5.3. Artigos
5.4. Códigos T-SQL
Neste item estão os códigos utilizados para criar as tabelas utilizadas nos exemplos, bem como carregá-las com dados.
5.4.1. tbVenda_2018
Criação da tabela de quantidades de itens vendidos mensalmente, no ano de 2018.
-- código #5.1 CREATE TABLE dbo.tbVenda_2018 ( Mês tinyint not null, Ano smallint not null, Qtd int not null ); INSERT into dbo.tbVenda_2018 (Mês, Ano, Qtd) values (1, 2018, 15), (2, 2018, 22), (3, 2018, 33), (4, 2018, 40), (5, 2018, 55), (6, 2018, 60), (7, 2018, 77), (8, 2018, 80), (9, 2018, 99), (10, 2018, 100), (11, 2018, 111), (12, 2018, 120);
5.4.2. tbVenda_Diaria
Gera massa de testes com dados de 1 de janeiro de 2017 até o último dia do mês anterior de execução do código.
-- código #5.2
CREATE TABLE dbo.tbVenda_Diaria (
Data_Venda datetime2(0) not null,
Cod_Loja tinyint not null,
Cod_Vendedor tinyint null,
Valor_Venda decimal(6,2) not null
);
-- gera massa de testes
declare @Loja_Vendedor table (Cod_Loja tinyint, Cod_Vendedor tinyint);
INSERT into @Loja_Vendedor (Cod_Loja, Cod_Vendedor) values
(1, 1), (1, 2), (2, 3), (2, 4), (2, 5), (3, 6), (3, 7);
declare @DataInicial datetime2, @DataFinal datetime;
set @DataInicial= convert (datetime2, '1/1/2017', 103);
set @DataFinal= dateadd (day,
-day (current_timestamp),
cast (current_timestamp as date));
with Datas as (
SELECT @DataInicial as Data
union all
SELECT dateadd (day, +7, R.Data)
from Datas as R
where dateadd (day, +7, R.Data) < @DataFinal
)
INSERT into dbo.tbVenda_Diaria
(Data_Venda, Cod_Loja, Cod_Vendedor, Valor_Venda)
SELECT D.Data, LV.Cod_Loja, LV.Cod_Vendedor,
(day (D.Data) * LV.Cod_Vendedor * 5)
from Datas as D
cross join @Loja_Vendedor as LV
where day (D.Data) <> Cod_Vendedor
option (maxrecursion 150);
5.4.3. tabela tbProduto
Criação de tabela de produtos
-- código #5.3
CREATE TABLE dbo.tbProduto (
Produto int not null,
Categoria char(2) not null,
Metros decimal(5,2) not null,
Peso decimal (6,3) not null
);
INSERT into dbo.tbProduto (Produto, Categoria, Metros, Peso)
values (1, '01', 100.00, 30.000),
(1, '02', 20.00, 6.000),
(1, 'RT', 5.00, 1.500);
| Este artigo também está disponível em arquivo no formato PDF, o que possibilita tanto visualizar online quanto obter cópia (download) para leitura offline e até mesmo impressão. Clique no texto abaixo para obter o artigo completo. |
Porto SQL 