Olá pessoas,
Após conhecer um pouco do UNPIVOT, percebe-se que este comando não é muito flexível, mesmo sendo um pouco mais maleável que o comando PIVOT. Por desconhecer este comando, ou pela falta de flexibilidade, é comum encontrar outras alternativas para ‘transformar colunas em linhas’, como o exemplo abaixo:
-- Criando uma tabela de teste: DECLARE @CONTAS TABLE ( [BANCO] VARCHAR(100), [ANO] SMALLINT, [INVESTIMENTOS] MONEY, [DESPESAS] MONEY ) INSERT INTO @CONTAS VALUES ('BANCO ALVORADA S/A', 2010, 9613906084.01, 8102644.84), ('BANCO ALVORADA S/A', 2011, 174343.35, 7935411.15), ('BANCO ARBI S/A', 2010, 8202652.29, 114215.13), ('BANCO ARBI S/A', 2011, 8407843.72, 81746.25) -- Uso do UNION ALL para transformar colunas em linhas: SELECT [BANCO], [ANO], 'INVESTIMENTOS' AS [TIPO], C.[INVESTIMENTOS] AS [VALOR] FROM @CONTAS C UNION ALL SELECT [BANCO], [ANO], 'DESPESAS' AS [TIPO], C.[DESPESAS] AS [VALOR] FROM @CONTAS C ORDER BY [BANCO], [ANO], [TIPO]
Esta estratégia com UNION ALL requer uma leitura da tabela a mais para cada coluna transformada em linha, ou seja, a tabela no exemplo acima foi lida duas vezes para obter o mesmo resultado que poderia ser obtido lendo a tabela uma única vez com UNPIVOT:
SELECT [BANCO], [ANO], [TIPO], [VALOR] FROM @CONTAS C UNPIVOT ( [VALOR] FOR [TIPO] IN ( [INVESTIMENTOS], [DESPESAS] ) ) AS U ORDER BY [BANCO], [ANO], [TIPO]
Com o STATISTICS IO, podemos obter as seguintes informações:
Consulta com UNION ALL:
(8 row(s) affected)
Table ‘#A255182D’. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Consulta com UNPIVOT:
(8 row(s) affected)
Table ‘#A255182D’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Mas como obter flexibilidade, tendo um número de leituras semelhante ao UNPIVOT, e até mesmo ter um plano de execução um pouco melhor?
A resposta pode ser encontrada no CROSS APPLY – que para quem não conhece, este comando funciona de forma um pouco semelhante ao JOIN, executando uma determinada subquery para cada registro da tabela com a qual ele faz o ‘JOIN’ –, que pode ser utilizado com UNION ALL para transformar colunas em linhas:
SELECT [BANCO], [ANO], [TIPO], [VALOR] FROM @CONTAS C CROSS APPLY ( SELECT 'INVESTIMENTOS' AS [TIPO], C.[INVESTIMENTOS] AS [VALOR] UNION ALL SELECT 'DESPESAS' AS [TIPO], C.[DESPESAS] AS [VALOR] ) AS U ORDER BY [BANCO], [ANO], [TIPO]
Ou combinado ao VALUES:
SELECT [BANCO], [ANO], [TIPO], [VALOR] FROM @CONTAS C CROSS APPLY ( VALUES ('INVESTIMENTOS', C.[INVESTIMENTOS]), ('DESPESAS', C.[DESPESAS]) ) AS U ([TIPO], [VALOR]) ORDER BY [BANCO], [ANO], [TIPO]
Em breve, vou publicar um comparativo mais detalhado entre o UNPIVOT e o CROSS APPLY!
só falta dizer que funciona apenas no oracle 11 R1 ou posterior. 😥