Olá! Seja bem-vindo!

Hoje veremos como usar o PIVOT com SOMATÓRIA de COLUNAS e LINHAS, uma técnica complementar as que vimos nos vídeos anteriores sobre esse assunto (SELECT com PIVOT).

O que aprendemos até agora

Esse assunto deu o que falar, certo? Esse é o quarto vídeo da série sobre PIVOT, então estou incluindo os links aqui nesse artigo para que você tenha tudo em um só local:

Como transformar linhas em colunas

No primeiro vídeo de nossa série te mostrei a sintaxe básica do PIVOT: https://dba-pro.com/como-transformar-linhas-em-colunas-no-sql-server

Como executar PIVOT com colunas dinâmicas

No segundo vídeo, te mostrei como trabalhar com PIVOT em um cenário de colunas dinâmicas, assim você não precisa alterar sua query a cada mudança de informações na base (ex: ano ou nomes de colaboradores): https://dba-pro.com/pivot-com-colunas-dinamicas-no-sql-server/

Como executar PIVOT com somatório das colunas

No terceiro vídeo te mostrei como criar um somatório nas colunas PIVOTEADAS: https://dba-pro.com/pivot-com-somatoria-das-colunas-no-sql

PIVOT com SOMATÓRIA de COLUNAS e LINHAS

Atendendo a pedidos dos alunos, hoje iremos para o próximo passo. Além de fazermos a somatória das colunas, para termos o montante vendido ano a ano, montaremos também um somatório das linhas, para sabermos de forma simplificada o quanto cada colaborador vendeu no total.

Aqui vai o script da aula:


------------------------------------------------------------------
-- PIVOT: Transposição (PIVOT) + Totalizadores de colunas e linhas (GROUPING SETS)
------------------------------------------------------------------
--------------------------------------------------
-- 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 
	('Abismail do Rosário'),
	('Josemar Pinto Godinho'),
	('Francisco Sanguessuga'),
	('Rolando da Rocha'),
	('Necrotério Silveira'),
	('Navegador da Luz')

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 -------------------------------------------------- -- Transposição + Somatória -------------------------------------------------- -- Transposição simples: select * from ( select year(dt) as Ano, c.nm as Colaborador, sum(vl_venda) as Valor from vendas join colaboradores c on c.id = vendas.id_vendedor group by c.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 somatória de colunas - referências: módulo 3 vídeos 12 (agrupamentos) e 17 (transposição) -- 1) Coalesce da coluna NOME para incluir o total por ano -- 2) Trocar group by por grouping sets e adicionar uma somatória por ano select * from ( select year(dt) as Ano, coalesce(c.nm, 'TOTAIS DO ANO =>') as Colaborador,
		sum(vl_venda) as Valor
	from vendas 
	join colaboradores c on c.id = vendas.id_vendedor
	--group by c.nm, year(dt) -- substituimos group by por grouping sets
	group by grouping sets (
		(c.nm, year(dt)), -- agrupamento padrão (igual ao group by)
		(year(dt)) -- somatória por ano
		) 
) em_linha
pivot (sum(valor) for ano in ([2013], [2014], [2015], [2016], [2017], [2018])) em_colunas 
order by 1
go


-- Transposição com somatória de colunas / linhas / total geral: Referência Módulo 3 (aulas 12 e 17)
-- 1) Coalesce no ano
-- 2) Adicionar somatórias "por nome" e "total geral" ao grouping sets
-- 3) Incluir coluna total do colaborador no pivot
select *
from (
	select 
		coalesce(cast(year(dt) as varchar(50)), 'TOTAL DO COLABORADOR') as Ano,
		coalesce(c.nm, 'TOTAL DO ANO =>') as Colaborador,
		sum(vl_venda) as Valor
	from vendas 
	join colaboradores c on c.id = vendas.id_vendedor
	group by grouping sets (
		(c.nm, year(dt)),
		(year(dt)),
		(c.nm),
		()
		) 
) em_linha
pivot (sum(valor) for ano in ([2013], [2014], [2015], [2016], [2017], [2018], [TOTAL DO COLABORADOR])) em_colunas 
order by 1
go

--------------------------------------------------
-- Excluindo dados de teste
--------------------------------------------------
use master
drop database curso

CONCLUSÃO

Se você já está em meu curso de SQL sabe o quanto eu gosto de misturar os assuntos, e essa série sobre PIVOT foi providencial para isso, concorda? Vimos SELECT, CTE, JOINS, cláusula XML, string functions, PIVOT e agora GROUPING SETS, somando tanto colunas, quanto linhas.

Espero que você também tenha achado esses conteúdos simples, divertidos e úteis.

Abraço do seu amigo Josué 🙂