Olá pessoas,
A mágica de transformar linhas em colunas no SQL Server com PIVOT não é tão simples, o que requer um pouco de domínio de T-SQL, para saber como utiliza-la, além de não ser pego por alguns erros comuns.
Neste primeiro artigo, vou apresentar alguns exemplos simples, e apresentar um problema comum de se trabalhar com PIVOT.
De início, temos uma massa de dados:
DECLARE @CONTAS TABLE ( ANO SMALLINT, BANCO VARCHAR(100), TIPO VARCHAR(100), VALOR MONEY ) INSERT INTO @CONTAS VALUES (2009,'BANCO ALVORADA S/A','INVESTIMENTOS',6175979775.42), (2010,'BANCO ALVORADA S/A','INVESTIMENTOS',6486892688.53), (2011,'BANCO ALVORADA S/A','INVESTIMENTOS',7905663406.86), (2012,'BANCO ALVORADA S/A','INVESTIMENTOS',9613906084.01), (2009,'BANCO ARBI S/A','INVESTIMENTOS',8102644.84), (2009,'BANCO ARBI S/A','OUTROS',174343.35), (2010,'BANCO ARBI S/A','INVESTIMENTOS',7935411.15), (2010,'BANCO ARBI S/A','OUTROS',119885.82), (2011,'BANCO ARBI S/A','INVESTIMENTOS',8202652.29), (2011,'BANCO ARBI S/A','OUTROS',114215.13), (2012,'BANCO ARBI S/A','INVESTIMENTOS',8407843.72), (2012,'BANCO ARBI S/A','OUTROS',81746.25) SELECT ANO, BANCO, TIPO, VALOR FROM @CONTAS
Para criar transformar as linhas de contas do tipo ‘INVESTIMENTOS’ e ‘OUTROS’ em linhas com uma coluna com os valores respectivos a ‘INVESTIMENTOS’ e outra a ‘OUTROS’, utilizaremos a expressão PIVOT sobre a tabela @CONTAS, somando os valores da coluna ‘VALOR’ para onde a coluna ‘TIPO’ apresenta os valores ‘INVESTIMENTOS’ e ‘OUTROS’, assim temos a primeira consulta e resultado:
SELECT U.ANO, U.BANCO, U.INVESTIMENTOS, U.OUTROS FROM @CONTAS AS C PIVOT ( SUM(VALOR) FOR TIPO IN (INVESTIMENTOS, OUTROS) ) AS U
Desta forma, os registros da tabela @CONTAS se transformam na PIVOT com o alias/apelido U (pode ser outra alias, sem problemas), assim as colunas TIPO e VALOR deixaram de existir, e são agrupadas por ANO e BANCO nas colunas INVESTIMENTOS e OUTROS.
Obs.: Não é obrigatório especificar as colunas no SELECT, pois ele só reconhecerá as colunas do alias U, mas especifiquem para ficar organizado.
Abaixo, um exemplo utilizando a coluna VALOR agrupada por BANCO e TIPO para cada um dos anos da coluna ANO:
SELECT U.BANCO, U.TIPO, U.[2009], U.[2010], U.[2011], U.[2012] FROM @CONTAS AS C PIVOT ( SUM(C.VALOR) FOR C.ANO IN ([2009], [2010], [2011], [2012]) ) AS U
Uma preocupação que se deve ter ao utilizar PIVOT é especificar claramente as colunas que serão agrupadas e quais as colunas que não serão, pois o SQL Server ainda não lê pensamentos, o que gera um erro bem comum, como apresentado abaixo.
“Então, estou com aquele script de PIVOT (A), mas quando removi a coluna ANO, para agrupar pelas colunas BANCO, INVESTIMENTOS e OUTROS, mas não deu certo, começou a aparecer duplicado a coluna BANCO (B).”
(A):
SELECT U.BANCO, U.INVESTIMENTOS, U.OUTROS FROM @CONTAS AS C PIVOT ( SUM(VALOR) FOR TIPO IN (INVESTIMENTOS, OUTROS) ) AS U
(B):
Este problema se dá ao fato, que independente de você tirar as colunas especificadas no SELECT, a PIVOT continuará sendo feita sobre todas as colunas da tabela, ou seja, tirou a coluna ‘ANO’ do SELECT, não quer dizer que a PIVOT vai esquecer que a coluna ‘ANO’ existe.
Para evitar este problema, ao invés de utilizar diretamente a tabela para a PIVOT, especifique em uma subconsulta, quais colunas a PIVOT utilizará, exemplo:
SELECT U.BANCO, U.INVESTIMENTOS, U.OUTROS FROM ( SELECT BANCO, TIPO, VALOR FROM @CONTAS ) AS C PIVOT ( SUM(C.VALOR) FOR C.TIPO IN (INVESTIMENTOS, OUTROS) ) AS U
Próximas semanas, eu vou apresentar o UNPIVOT e também ensinar as limitações destas cláusulas e mágicas, assim como explicar como fazer as mesmas coisas sem utilizar PIVOT e UNPIVOT.
Pingback: Transformando colunas em linhas sem UNPIVOT | SQL From Hell.com