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
http://www.sqlfromhell.com/numero-de-linhas-de-todas-tabelas-de-um-banco-de-dados-do-azure/
http://www.sqlfromhell.com/usuarios-orfaos-gerando-o-script-de-create-login-com-os-sids/
SQL From Hell.com 