Olá pessoas,
Na semana passada, nós vimos como transformar linhas em colunas no SQL Server com PIVOT. Mas como nem sempre dá para contar com este recurso, e algumas vezes, ele não supre 100% das nossas necessidades, vou apresentar alguns exemplos de como fazer PIVOT sem utilizar PIVOT.
Para estes exemplos, estarei utilizando da mesma massa de dados da semana anterior:
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)
Conforme o primeiro exemplo da semana anterior, transformamos os registros de contas do tipo INVESTIMENTOS e OUTROS em duas colunas, utilizando PIVOT:
SELECT U.ANO, U.BANCO, U.INVESTIMENTOS, U.OUTROS
FROM @CONTAS AS C
PIVOT (
SUM(C.VALOR) FOR
C.TIPO IN (INVESTIMENTOS, OUTROS)
) AS U
Sem PIVOT, é possível fazer o mesmo procedimento com CASE WHEN:
SELECT C.ANO, C.BANCO,
INVESTIMENTOS = SUM(CASE WHEN C.TIPO = 'INVESTIMENTOS' THEN C.VALOR END),
OUTROS = SUM(CASE WHEN C.TIPO = 'OUTROS' THEN C.VALOR END)
FROM @CONTAS AS C
GROUP BY C.ANO, C.BANCO
E no SQL Server 2012+, com IIF:
SELECT C.ANO, C.BANCO,
INVESTIMENTOS = SUM(IIF(C.TIPO = 'INVESTIMENTOS', C.VALOR, NULL)),
OUTROS = SUM(IIF(C.TIPO = 'OUTROS', C.VALOR, NULL))
FROM @CONTAS AS C
GROUP BY C.ANO, C.BANCO
Também é possível fazer com subqueries, mas como não é performático, então ignoraremos esta alternativa.
O segundo exemplo, transformamos os registros de anos em colunas respectivas a estes anos, utilizando PIVOT:
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
Sem PIVOT, é possível fazer o mesmo procedimento com CASE WHEN:
SELECT C.BANCO, C.TIPO,
[2009] = SUM(CASE WHEN C.ANO = 2009 THEN C.VALOR END),
[2010] = SUM(CASE WHEN C.ANO = 2010 THEN C.VALOR END),
[2011] = SUM(CASE WHEN C.ANO = 2011 THEN C.VALOR END),
[2012] = SUM(CASE WHEN C.ANO = 2012 THEN C.VALOR END)
FROM @CONTAS AS C
GROUP BY C.BANCO, C.TIPO
E no SQL Server 2012+, com IIF:
SELECT C.BANCO, C.TIPO,
[2009] = SUM(IIF(C.ANO = 2009, C.VALOR, NULL)),
[2010] = SUM(IIF(C.ANO = 2010, C.VALOR, NULL)),
[2011] = SUM(IIF(C.ANO = 2011, C.VALOR, NULL)),
[2012] = SUM(IIF(C.ANO = 2012, C.VALOR, NULL))
FROM @CONTAS AS C
GROUP BY C.BANCO, C.TIPO
E agora, algumas questões que são mais fáceis de resolver com CASE WHEN e IIF, como criar regras mais específicas para as colunas utilizando outras funções e outras regras de agrupamento:
SELECT U.BANCO, U.TIPO,
U.[2008], U.[2009], U.[2010], U.[2011], U.[2012],
[2009_2010] = U.[2009] + U.[2010],
[2011_2012] = U.[2011] + U.[2012],
TOTAL = U.[2009] + U.[2010] + U.[2011] + U.[2012]
FROM @CONTAS AS C
PIVOT (
SUM(C.VALOR) FOR
C.ANO IN ([2008], [2009], [2010], [2011], [2012])
) AS U
SELECT C.BANCO, C.TIPO,
[2008] = SUM(CASE WHEN C.ANO = 2008 THEN C.VALOR ELSE 0 END),
[2009] = SUM(CASE WHEN C.ANO = 2009 THEN C.VALOR ELSE 0 END),
[2010] = SUM(CASE WHEN C.ANO = 2010 THEN C.VALOR ELSE 0 END),
[2011] = SUM(CASE WHEN C.ANO = 2011 THEN C.VALOR ELSE 0 END),
[2012] = SUM(CASE WHEN C.ANO = 2012 THEN C.VALOR ELSE 0 END),
[!2012] = SUM(CASE WHEN C.ANO <> 2012 THEN C.VALOR ELSE 0 END),
[<2012] = SUM(CASE WHEN C.ANO < 2012 THEN C.VALOR ELSE 0 END),
[2009_2010] = SUM(CASE WHEN C.ANO IN (2009, 2010) THEN C.VALOR ELSE 0 END),
[2011_2012] = SUM(CASE WHEN C.ANO BETWEEN 2011 AND 2012 THEN C.VALOR ELSE 0 END),
TOTAL = SUM(C.VALOR),
MEDIA = AVG(C.VALOR)
FROM @CONTAS AS C
GROUP BY C.BANCO, C.TIPO
SELECT C.BANCO, C.TIPO,
[2008] = SUM(IIF(C.ANO = 2008, C.VALOR, 0)),
[2009] = SUM(IIF(C.ANO = 2009, C.VALOR, 0)),
[2010] = SUM(IIF(C.ANO = 2010, C.VALOR, 0)),
[2011] = SUM(IIF(C.ANO = 2011, C.VALOR, 0)),
[2012] = SUM(IIF(C.ANO = 2012, C.VALOR, 0)),
[!2012] = SUM(IIF(C.ANO <> 2012, C.VALOR, 0)),
[<2012] = SUM(IIF(C.ANO < 2012, C.VALOR, 0)),
[2009_2010] = SUM(IIF(C.ANO IN (2009, 2010), C.VALOR, 0)),
[2011_2012] = SUM(IIF(C.ANO BETWEEN 2011 AND 2012, C.VALOR, 0)),
TOTAL = SUM(C.VALOR),
MEDIA = AVG(C.VALOR)
FROM @CONTAS AS C
GROUP BY C.BANCO, C.TIPO
E a composição de um agrupamento de linhas para colunas com considerando mais de uma coluna:
SELECT BANCO = COALESCE(U_I.BANCO, U_O.BANCO),
INV_2009 = U_I.[2009],
INV_2010 = U_I.[2010],
INV_2011 = U_I.[2011],
INV_2012 = U_I.[2012],
OUT_2009 = U_O.[2009],
OUT_2010 = U_O.[2010],
OUT_2011 = U_O.[2011],
OUT_2012 = U_O.[2012]
FROM
(SELECT BANCO, ANO, VALOR FROM @CONTAS WHERE TIPO = 'INVESTIMENTOS') AS C_I
PIVOT (
SUM(C_I.VALOR) FOR
C_I.ANO IN ([2009], [2010], [2011], [2012])
) AS U_I
FULL OUTER JOIN
(SELECT BANCO, ANO, VALOR FROM @CONTAS WHERE TIPO = 'OUTROS') AS C_O
PIVOT (
SUM(C_O.VALOR) FOR
C_O.ANO IN ([2009], [2010], [2011], [2012])
) AS U_O
ON U_I.BANCO = U_O.BANCO
SELECT C.BANCO,
INV_2009 = SUM(CASE WHEN C.ANO = 2009 AND TIPO = 'INVESTIMENTOS' THEN C.VALOR ELSE 0 END),
INV_2010 = SUM(CASE WHEN C.ANO = 2010 AND TIPO = 'INVESTIMENTOS' THEN C.VALOR ELSE 0 END),
INV_2011 = SUM(CASE WHEN C.ANO = 2011 AND TIPO = 'INVESTIMENTOS' THEN C.VALOR ELSE 0 END),
INV_2012 = SUM(CASE WHEN C.ANO = 2012 AND TIPO = 'INVESTIMENTOS' THEN C.VALOR ELSE 0 END),
OUT_2009 = SUM(CASE WHEN C.ANO = 2009 AND TIPO = 'OUTROS' THEN C.VALOR ELSE 0 END),
OUT_2010 = SUM(CASE WHEN C.ANO = 2010 AND TIPO = 'OUTROS' THEN C.VALOR ELSE 0 END),
OUT_2011 = SUM(CASE WHEN C.ANO = 2011 AND TIPO = 'OUTROS' THEN C.VALOR ELSE 0 END),
OUT_2012 = SUM(CASE WHEN C.ANO = 2012 AND TIPO = 'OUTROS' THEN C.VALOR ELSE 0 END)
FROM @CONTAS AS C
GROUP BY C.BANCO
SELECT C.BANCO,
INV_2009 = SUM(IIF(C.ANO = 2009 AND TIPO = 'INVESTIMENTOS', C.VALOR, 0)),
INV_2010 = SUM(IIF(C.ANO = 2010 AND TIPO = 'INVESTIMENTOS', C.VALOR, 0)),
INV_2011 = SUM(IIF(C.ANO = 2011 AND TIPO = 'INVESTIMENTOS', C.VALOR, 0)),
INV_2012 = SUM(IIF(C.ANO = 2012 AND TIPO = 'INVESTIMENTOS', C.VALOR, 0)),
OUT_2009 = SUM(IIF(C.ANO = 2009 AND TIPO = 'OUTROS', C.VALOR, 0)),
OUT_2010 = SUM(IIF(C.ANO = 2010 AND TIPO = 'OUTROS', C.VALOR, 0)),
OUT_2011 = SUM(IIF(C.ANO = 2011 AND TIPO = 'OUTROS', C.VALOR, 0)),
OUT_2012 = SUM(IIF(C.ANO = 2012 AND TIPO = 'OUTROS', C.VALOR, 0))
FROM @CONTAS AS C
GROUP BY C.BANCO