Hoje aprenderemos como fazer PIVOT com 2 COLUNAS ou mais no SQL Server, atendendo a pedidos que recebi de alguns alunos das redes sociais.
Antes de iniciarmos esse vídeo, deixo abaixo as referências para artigos relacionados:
– Como transformar linhas em colunas (PIVOT): https://dba-pro.com/como-transformar-linhas-em-colunas-no-sql-server/
– Como fazer PIVOT com colunas dinâmicas: https://dba-pro.com/pivot-com-colunas-dinamicas-no-sql-server/
– Como gerar somatórias das colunas do PIVOT: https://dba-pro.com/pivot-com-somatoria-das-colunas-no-sql/
– Como gerar somatórias de colunas e linhas do PIVOT: https://dba-pro.com/pivot-com-somatoria-de-colunas-e-linhas/
PIVOT com 2 COLUNAS ou mais
Aqui vai o script da aula de hoje, para você fazer testes no seu computador:
[sociallocker id=”5114″]
------------------------------------------------------------------
-- PIVOT: Transposição (PIVOT) + Multiplas Colunas
------------------------------------------------------------------
--------------------------------------------------
-- Banco de dados para nosso teste
--------------------------------------------------
-- Banco de testes
use master
if db_id('curso') is not null
drop database curso
go
create database curso
go
use curso
go
create table colaboradores (
id smallint identity(1,1) primary key,
nm varchar(100) not null,
)
insert into colaboradores values
('Colaborador 1'),
('Colaborador 2'),
('Colaborador 3'),
('Colaborador 4')
create table produtos (
id smallint identity(1,1) primary key,
nm varchar(100),
vl decimal(10,2),
ic_ativo bit not null default (1)
)
insert produtos (nm, vl) values
('Mouse Gammer 25 botões', 200),
('Teclado Gammer 350 teclas', 300),
('Monitor 32 Pol Full HD', 1000),
('RAM DDR4 4GB Powerturbo', 500),
('CPU Nasa 10Ghz', 2000),
('HD SSD 1TB', 2000)
create table vendas (
id int not null identity(1, 1) primary key,
dt datetime not null,
id_produto smallint not null foreign key references produtos(id),
id_vendedor smallint foreign key references colaboradores(id),
qt int check (qt > 0),
vl_unitario decimal (10,2),
vl_venda as qt * vl_unitario
)
;with cte_seq as (
select 1 as nr_cont
union all
select nr_cont + 1
from cte_seq where nr_cont < 10000)
insert into vendas (dt, id_produto, id_vendedor, qt, vl_unitario)
select
dateadd(day, -(abs(checksum(newid())) % 1825), getdate()) dt,
produto.id,
colaborador.id,
1 + (abs(checksum(newid())) % 15) nr_quantidade,
produto.vl vl_unitario
from cte_seq
left join (select top 1 id, vl from produtos order by newid()) produto on 1=1
left join (select top 1 id from colaboradores order by newid()) colaborador on 1=1
option (maxrecursion 0)
go
--------------------------------------------------
-- PIVOT básico
--------------------------------------------------
select
year(dt) as Ano,
p.nm as Produto,
sum(vl_venda) as Valor
from vendas
join produtos p on p.id = vendas.id_produto
group by p.nm, year(dt)
select
*
from (
select
year(dt) as Ano,
p.nm as Produto,
sum(vl_venda) as Valor
from vendas
join produtos p on p.id = vendas.id_produto
group by p.nm, year(dt)
) em_linha
pivot (sum(valor) for ano in ([2013], [2014], [2015], [2016], [2017], [2018])) em_colunas
order by 1
go
--------------------------------------------------
-- Transposição com multiplas colunas
--------------------------------------------------
-- Com PIVOT
select
produto,
sum(a2013q) as a2013q,
sum(a2013v) as a2013v,
sum(a2014q) as a2014q,
sum(a2014v) as a2014v,
sum(a2015q) as a2015q,
sum(a2015v) as a2015v,
sum(a2016q) as a2016q,
sum(a2016v) as a2016v,
sum(a2017q) as a2017q,
sum(a2017v) as a2017v,
sum(a2018q) as a2018q,
sum(a2018v) as a2018v
from (
select
*
from (
select
'a' + convert(varchar, year(dt)) + 'v' as Ano_V,
'a' + convert(varchar, year(dt)) + 'q' as Ano_Q,
p.nm as Produto,
sum(qt) as qtd,
sum(vl_venda) as Valor
from vendas
join produtos p on p.id = vendas.id_produto
group by p.nm, year(dt)
) em_linha
pivot (sum(valor) for Ano_V in ([a2013v], [a2014v], [a2015v], [a2016v], [a2017v], [a2018v])) em_colunas_v
pivot (sum(qtd) for Ano_Q in ([a2013q], [a2014q], [a2015q], [a2016q], [a2017q], [a2018q])) em_colunas_q
) a
group by produto
order by 1
go
-- Com SUM-CASE
select
p.nm as Produto,
sum(case year(dt) when 2013 then qt end) as a2013q,
sum(case year(dt) when 2013 then vl_venda end) as a2013v,
sum(case year(dt) when 2014 then qt end) as a2014q,
sum(case year(dt) when 2014 then vl_venda end) as a2014v,
sum(case year(dt) when 2015 then qt end) as a2015q,
sum(case year(dt) when 2015 then vl_venda end) as a2015v,
sum(case year(dt) when 2016 then qt end) as a2015q,
sum(case year(dt) when 2016 then vl_venda end) as a2016v,
sum(case year(dt) when 2017 then qt end) as a2017q,
sum(case year(dt) when 2017 then vl_venda end) as a2017v,
sum(case year(dt) when 2018 then qt end) as a2018q,
sum(case year(dt) when 2018 then vl_venda end) as a2018v
from vendas
join produtos p on p.id = vendas.id_produto
group by p.nm
-- Com SUM-CASE dinamico
declare @ano_de int = 2013
declare @ano_ate int = 2016
declare @sql as varchar(1000)
;with cte_anos as (
select
@ano_de as ano,
',sum(case year(dt) when ' + convert(varchar, @ano_de) + ' then qt end) as a' + convert(varchar, @ano_de) + 'q' as q,
',sum(case year(dt) when ' + convert(varchar, @ano_de) + ' then vl_venda end) as a' + convert(varchar, @ano_de) + 'v' as v
union all
select
ano + 1,
',sum(case year(dt) when ' + convert(varchar, ano + 1) + ' then qt end) as a' + convert(varchar, ano + 1) + 'q',
',sum(case year(dt) when ' + convert(varchar, ano + 1) + ' then vl_venda end) as a' + convert(varchar, ano + 1) + 'v'
from cte_anos where ano < @ano_ate
)
select @sql = (
select
q + v
from cte_anos
for xml path('')
)
set @sql = 'select p.nm as Produto ' + @sql + ' from vendas join produtos p on p.id = vendas.id_produto group by p.nm'
print @sql
execute (@sql)
--------------------------------------------------
-- Excluindo dados de teste
--------------------------------------------------
/*
use master
drop database curso
*/
[/sociallocker]
CONCLUSÃO
Somando esse artigo (PIVOT com 2 COLUNAS ou mais), agora temos 5 relacionados a PIVOT.
Desde o primeiro vídeo apareceram vários alunos interessados em se aprofundar, fiquei bastante feliz com isso.
Espero que também tenha gostado desse conteúdo.
Abraço do seu amigo Josué 🙂