UNPIVOT no SQL Server

Olá Caríssimos tudo bem ?

Em um post sobre banco de dados falei um pouco sobre o PIVOT no SQL. Um recurso bacana que gera uma “tabela dinâmica” transformando linhas em colunas no SQL Server.

E o contrário ? Transformar dado que estão em colunas em linhas ?

SIM!!! Isso é possível. Estava por esses dias fazendo uns desdobramentos para gerar combinações de números da Lotofácil (por isso talvez este seja meu último post, se eu ganhar vou tirar férias eternas!!!!) e precisei utilizar este recurso.

A tabela

Consultando no site da Caixa os resultados são baixados neste layout:

E o que preciso para conseguir gerar as estatísticas é esse layout:

Pra fazer isso é muito, muito simples, abra a query no SQL Server e siga o mesmo caminho abaixo:

SELECT Concurso, Dezenas,[Data Sorteio]
FROM
(SELECT Concurso,[Data Sorteio], Bola1, Bola2, Bola3, Bola4, Bola5, Bola6, Bola7, Bola8, Bola9, Bola10,
Bola11,Bola12,Bola13,Bola14,Bola15
FROM LOTFAC) PT
UNPIVOT
(Dezenas FOR Dez IN
(Bola1, Bola2, Bola3, Bola4, Bola5, Bola6, Bola7, Bola8, Bola9, Bola10,
Bola11,Bola12,Bola13,Bola14,Bola15)
)AS unpvt

Análise

1 – Observe que fiz uma subquery e dei o nome de PT, esta query deve ter todas as colunas que precisamos.

2 – Depois foi só utilizar o UNPIVOT para agregar os dados e colocar cada coluna como linha.

É SÓ ISSO!!!

Basta adaptar pra sua necessidade.

abraço a todos!!!

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!!

PIVOT no SQL Server parte I

Olá Caríssimos tudo bem ?

Neste post estarei mostrando como é simples realizar uma consulta para gerar um  resultado que seria como uma tabela de referência cruzada (pivot table, transposição de tabela, tabela dinâmica, etc) no SQL Server.

Vamos dividir o post em duas partes, nesta vamos fazer algo simples e no próximo post faremos algo mais avançado.

Uma referência cruzada ou Pivot Table é necessária quando queremos efetuar uma transposição nos dados, realizando algum cálculo (ou não).

Imagine o seguinte: Temos uma tabela com todas as vendas por marca e por cidade e desejamos exibir os resultados colocando as marcas como linhas e as cidades como colunas, assim cada marca terá somente uma linha na consulta final. Assim deixamos o resultado mais resumido e de fácil visualização.

A tabela

Nossa tabela VENDAS tem o layout assim:

Como citei anteriormente queremos visualizar as cidades como colunas e o layout final ficará assim:

Pra fazer isso é muito, muito simples, abra a query no SQL Server e siga o mesmo caminho abaixo:

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

Análise

1 – Observe que fiz uma subquery e dei o nome de PT, podíamos ter adicionado até uma função agregada. O importante é esta query ter todas as colunas que precisamos.

2 – Depois foi só utilizar o PIVOT para agregar os dados e colocar as linhas como colunas.

É SÓ ISSO!!!

Basta adaptar pra sua necessidade.

Você deve estar questionando  OK. E se minhas colunas forem dinâmicas, se variarem dependendo de um filtro ?

A resposta será dada no próximo POST (PIVOT no Sql Server parte II) .

abraço a todos!!!