Transformando linhas em colunas sem PIVOT

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

2 pensamentos sobre “Transformando linhas em colunas sem PIVOT”

Paula disse:

15/08/2013 às 16:43

Olá, preciso utilizar o pivot, em uma tabela que tem ID, NOME E DESCRIÇÃO …

Fazendo com que alguns resultados da coluna NOME vire coluna em outra tabela e o que tem em DESCRIÇÃO vire resultado em outra coluna, já tentei, mas não esta rolando.

Estou tentando fazer algo, que busque o que esta na coluna NOME pelo ID ..

Select ‘coluna1′, ‘coluna2′, ‘coluna3′,’coluna4′,’colunal5
FROM #tabela
Pivot (DESCRIÇÃO) for ID IN ( 1,10,3,29,30))

Responder
- Paulo R. Pereira disse:
  
  15/08/2013 às 18:33
  
  Boa noite Paula, PIVOT funciona unicamente para funções de agregação (SUM, COUNT…).
  
  Me envie algo mais detalhado sobre o que você está tentando fazer, que tento te ajudar, exemplo:
  
  Tenho isso:
  ID|Nome|Descricao
  1|Paulo|Cliente
  2|Pedro|Vendedor
  
  Quero isso:
  ID|Campo|Valor
  1|Nome|Paulo
  2|Nome|Pedro
  1|Descricao|Cliente
  2|Descricao|Vendedor
  
  Responder

Deixe um comentário Cancelar resposta

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

SQL From Hell.com

Um nome bem intuitivo para muita informação!

Transformando linhas em colunas sem PIVOT

2 pensamentos sobre “Transformando linhas em colunas sem PIVOT”

Deixe um comentário Cancelar resposta

Avalie isto:

Compartilhe isso:

Relacionado

2 pensamentos sobre “Transformando linhas em colunas sem PIVOT”

Deixe um comentário Cancelar resposta