PIVOT no SQL Server parte II

Olá Caríssimos tudo bem ?

No post anterior falei sobre como fazer um PIVOT simples no SQL Server. (veja aqui)

Só uma recaptulação rápida: Utilizamos o PIVOT quando queremos mostrar linhas como colunas (transpor) em uma tabela.

No nosso exemplo tínhamos uma tabela de Vendas como o Layout :

E fizemos a query para mostrar a tabela assim:

Colocamos todas as cidades como Colunas.

Fizemos a query de uma forma simples, adicionando ‘hard code‘  o nome das cidades.

Select Marca,[São Paulo],[Rio de Janeiro],[Salvador],[Tarabai],[Campina Grande]
 from
 (
 Select Marca,Cidade,TotalVendido from vendas
 ) PT

PIVOT
 ( SUM(TotalVendido) for Cidade in ([São Paulo],[Rio de Janeiro],[Salvador],[Tarabai],[Campina Grande])) pvt

No fim do post levantei a questão se caso os itens que queremos mostrar como Colunas, neste caso cidades, fossem dinâmicas e como ficaria a consulta. Isso seria bem útil também para datas, já que é fora de questão ficarmos colocando datas que sempre mudam diretamente na query.

Quando digo dinâmicas, quero dizer que não sabemos o que virá como dado e que estes dados podem variar com o tempo, filtro ou qualquer outra situação.

No caso de dados dinâmicos temos que fazer alguns ajustes na query e torná-la dinâmica também:

Passo 1: Separar os dados que serão colunas

Vamos selecionar os valores que se tornarão colunas (no nosso caso eles estão na mesma tabela, mas não faz diferença se estiver em outra tabela relacionada) já no formato que queremos.

declare @values as nvarchar(max)
SELECT  @values = STUFF(( SELECT DISTINCT
'],[' + cidade
FROM    Vendas
ORDER BY '],[' + cidade
FOR XML PATH('')
), 1, 2, '') + ']'

Esse código irá gerar algo do tipo:

[Campina Grande],[Rio de Janeiro],[Salvador],[São Paulo],[Tarabai]

Passo 2: Preparar o esqueleto da query

Agora vamos criar o esqueleto da consulta final:

declare @query as nvarchar(max)
set @query = 'Select Marca, |@
from
(
Select Marca,Cidade,TotalVendido from vendas
) PT

PIVOT
( SUM(TotalVendido) for Cidade in ( |@ )) pvt'

OBS: Na query acima coloquei o marcador ‘|@’, que será substituído pelos valores das cidades.

Passo 3: Substituir os marcadores pelos valores obtidos no passo 1, executar a query e correr pro abraço!!!!!!!

set @query = REPLACE(@query,'|@',@values)
exec(@query)

Nosso código completo:

--- Separando os valores: Passo 1
declare @values as nvarchar(max)
SELECT  @values = STUFF(( SELECT DISTINCT
'],[' + cidade
FROM    Vendas
ORDER BY '],[' + cidade
FOR XML PATH('')
), 1, 2, '') + ']'

--- Esqueleto da query com os marcadores: Passo 2
declare @query as nvarchar(max)
set @query = 'Select Marca, |@
from
(
Select Marca,Cidade,TotalVendido from vendas
) PT

PIVOT
( SUM(TotalVendido) for Cidade in ( |@ )) pvt'
set @query = REPLACE(@query,'|@',@values)
--- Executando a query
exec(@query)

É isso, está feito.
Não é o melhor dos mundos esta solução mas não há outra forma.

Abraço a todos!!