Olá! Seja bem-vindo!
Hoje veremos como usar o comando PIVOT com SOMATÓRIA das COLUNAS no SQL, simplificando ainda mais a organização de informações para sua análise e criação de relatórios gerenciais.
Respondendo as dúvidas do vídeo anterior
Depois de dois vídeos sobre PIVOT (https://dba-pro.com/como-transformar-linhas-em-colunas-no-sql-server/) e (https://dba-pro.com/pivot-com-colunas-dinamicas-no-sql-server/), surgiu uma dúvida sobre como criar uma somatória das colunas apresentadas no PIVOT, facilitando mais a análise das informações ano a ano.
Para realizar essa tarefa substituiremos o agrupamento (GROUP BY) pelo agrupamento (GROUPING SETS), que é bem simples de utilizar e fará com que nosso PIVOT com SOMATÓRIAS de COLUNAS no SQL seja extremamente simplificado.
Confira no vídeo a resposta a essa dúvida.
PIVOT com SOMATÓRIA das COLUNAS no SQL
Aqui vai o script que usamos no vídeo de hoje:
------------------------------------------------------------------ -- PIVOT: Transposição (PIVOT) + Totalizadores de colunas (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 ('Colaborador 1'), ('Colaborador 2'), ('Colaborador 3'), ('Colaborador 4'), ('Colaborador 5'), ('Colaborador 6') 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 -------------------------------------------------- -- Excluindo dados de teste -------------------------------------------------- use master drop database curso
CONCLUSÃO
No vídeo de hoje vimos como uma técnica super simples pode fazer uma grande diferença na percepção de valor de um conjunto de informações.
Fato é, quanto mais simples conseguimos deixar o visual de uma informação, melhor, sabe porquê? Porque liberamos os cérebros das pessoas para elas focarem no negócio e em seu trabalho de análise, deixando a parte de TECNOLOGIA E MINERAÇÃO DE DADOS para nós.
E vale a pena focarmos nossas carreiras nisso? Se você tem dúvidas, dá uma olhada no vídeo que postei semana passada… (https://dba-pro.com/vale-a-pena-aprender-sql).
Abraço do seu amigo Josué 🙂