Transformando colunas em linhas sem UNPIVOT

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!

Transformando colunas em linhas com UNPIVOT

Olá pessoas,

O UNPIVOT é um pouco menos conhecido que o PIVOT, por razões óbvias, dentre elas, “é fácil transformar colunas em linhas”. Mas isso não limita o fato de quem já está acostumado com PIVOT, também utilize o UNPIVOT como recurso, principalmente por apresentarem sintaxes bem semelhantes.

UNPIVOT não utiliza funções de agregação como o PIVOT, mas é necessária capacidade abstrair duas colunas, sendo a primeira, a coluna que apresentará o nome da coluna que virou linha, e a segunda, a coluna que apresentará o valor da coluna que virou linha.

Para o primeiro exemplo, temos uma tabela com as colunas INVESTIMENTOS e DESPESAS, e transformo ‘investimentos’ e ‘despesas’ em valores da coluna TIPO, e os valores destas colunas coloco na coluna VALOR:

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)

SELECT [BANCO], [ANO], [TIPO], [VALOR]
FROM @CONTAS C
UNPIVOT (
	[VALOR] FOR [TIPO] IN (	
		[INVESTIMENTOS], 
		[DESPESAS]
	)
) AS U
ORDER BY [BANCO], [ANO], [TIPO]

Para o segundo exemplo, temos uma tabela com as colunas 2010 e 2011, e transformo ‘2010’ e ‘2011’ em valores da coluna ANO, e os valores destas colunas coloco na coluna VALOR:

DECLARE @CONTAS TABLE (
	[BANCO] VARCHAR(100), 
	[TIPO] VARCHAR(100),
	[2010] MONEY, 
	[2011] MONEY
)

INSERT INTO @CONTAS VALUES
('BANCO ALVORADA S/A', 'INVESTIMENTOS', 9613906084.01, 174343.35),
('BANCO ALVORADA S/A', 'DESPESAS', 8102644.84, 7935411.15),
('BANCO ARBI S/A', 'INVESTIMENTOS', 8202652.29, 8407843.72),
('BANCO ARBI S/A', 'DESPESAS', 114215.13, 81746.25)

SELECT [BANCO], [ANO], [TIPO], [VALOR]
FROM @CONTAS C
UNPIVOT (
	[VALOR] FOR [ANO] IN (	
		[2010], 
		[2011]
	)
) AS U
ORDER BY [BANCO], [ANO], [TIPO]

Para este terceiro exemplo, temos colunas juntos os nomes são formados tanto pelo tipo da conta e o ano, o que requer algumas transformações nos valores da coluna CONTA, para identificarmos o ano e o tipo da conta.

DECLARE @CONTAS TABLE (
	[BANCO] VARCHAR(100), 
	[INVESTIMENTOS_2010] MONEY, 
	[INVESTIMENTOS_2011] MONEY, 
	[DESPESAS_2010] MONEY, 
	[DESPESAS_2011] MONEY
)

INSERT INTO @CONTAS VALUES
('BANCO ALVORADA S/A', 9613906084.01, 8102644.84, 
174343.35, 7935411.15),
('BANCO ARBI S/A',8202652.29, 114215.13, 
8407843.72, 81746.25)

SELECT [BANCO], 
[ANO] = RIGHT([CONTA], CHARINDEX('_', REVERSE([CONTA])) - 1), 
[TIPO] = LEFT([CONTA], CHARINDEX('_', [CONTA]) - 1), 
[VALOR]
FROM @CONTAS C
UNPIVOT (
	[VALOR] FOR [CONTA] IN (	
		[INVESTIMENTOS_2010], 
		[INVESTIMENTOS_2011], 
		[DESPESAS_2010], 
		[DESPESAS_2011]
	)
) AS U
ORDER BY [BANCO], [ANO], [TIPO]

Agora, neste quarto exemplo, temos a transformação de diversas colunas em registros, o que requer unicamente que estas colunas sejam do mesmo tipo, no caso VARCHAR(100), para não dar o erro:

The type of column “xxxxx” conflicts with the type of other columns specified in the UNPIVOT list.

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)

SELECT [COLUNA], [VALOR]
FROM (
	SELECT 
		[BANCO],
		[ANO] = CAST([ANO] AS VARCHAR(100)),
		[INVESTIMENTOS] = CAST([INVESTIMENTOS] AS VARCHAR(100)),
		[DESPESAS] = CAST([DESPESAS] AS VARCHAR(100))
	FROM @CONTAS
) C
UNPIVOT (
	[VALOR] FOR [COLUNA] IN (	
		[INVESTIMENTOS], 
		[DESPESAS], 
		[BANCO],
		[ANO]
	)
) AS U