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

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

Transformando linhas em colunas com PIVOT

Olá pessoas,

A mágica de transformar linhas em colunas no SQL Server com PIVOT não é tão simples, o que requer um pouco de domínio de T-SQL, para saber como utiliza-la, além de não ser pego por alguns erros comuns.

Neste primeiro artigo, vou apresentar alguns exemplos simples, e apresentar um problema comum de se trabalhar com PIVOT.

De início, temos uma massa de dados:

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)

SELECT ANO, BANCO, TIPO, VALOR FROM @CONTAS

Para criar transformar as linhas de contas do tipo ‘INVESTIMENTOS’ e ‘OUTROS’ em linhas com uma coluna com os valores respectivos a ‘INVESTIMENTOS’ e outra a ‘OUTROS’, utilizaremos a expressão PIVOT sobre a tabela @CONTAS, somando os valores da coluna ‘VALOR’ para onde a coluna ‘TIPO’ apresenta os valores ‘INVESTIMENTOS’ e ‘OUTROS’, assim temos a primeira consulta e resultado:

SELECT U.ANO, U.BANCO, U.INVESTIMENTOS, U.OUTROS
FROM @CONTAS AS C
PIVOT (
  SUM(VALOR) FOR
  TIPO IN (INVESTIMENTOS, OUTROS)
) AS U

Desta forma, os registros da tabela @CONTAS se transformam na PIVOT com o alias/apelido U (pode ser outra alias, sem problemas), assim as colunas TIPO e VALOR deixaram de existir, e são agrupadas por ANO e BANCO nas colunas INVESTIMENTOS e OUTROS.

Obs.: Não é obrigatório especificar as colunas no SELECT, pois ele só reconhecerá as colunas do alias U, mas especifiquem para ficar organizado.

Abaixo, um exemplo utilizando a coluna VALOR agrupada por BANCO e TIPO para cada um dos anos da coluna ANO:

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

Uma preocupação que se deve ter ao utilizar PIVOT é especificar claramente as colunas que serão agrupadas e quais as colunas que não serão, pois o SQL Server ainda não lê pensamentos, o que gera um erro bem comum, como apresentado abaixo.

“Então, estou com aquele script de PIVOT (A), mas quando removi a coluna ANO, para agrupar pelas colunas BANCO, INVESTIMENTOS e OUTROS, mas não deu certo, começou a aparecer duplicado a coluna BANCO (B).”

(A):

SELECT U.BANCO, U.INVESTIMENTOS, U.OUTROS
FROM @CONTAS AS C
PIVOT (
  SUM(VALOR) FOR
  TIPO IN (INVESTIMENTOS, OUTROS)
) AS U

(B):

Este problema se dá ao fato, que independente de você tirar as colunas especificadas no SELECT, a PIVOT continuará sendo feita sobre todas as colunas da tabela, ou seja, tirou a coluna ‘ANO’ do SELECT, não quer dizer que a PIVOT vai esquecer que a coluna ‘ANO’ existe.

Para evitar este problema, ao invés de utilizar diretamente a tabela para a PIVOT, especifique em uma subconsulta, quais colunas a PIVOT utilizará, exemplo:

SELECT U.BANCO, U.INVESTIMENTOS, U.OUTROS
FROM (
  SELECT BANCO, TIPO, VALOR
  FROM @CONTAS
) AS C
PIVOT (
  SUM(C.VALOR) FOR
  C.TIPO IN (INVESTIMENTOS, OUTROS)
) AS U

Próximas semanas, eu vou apresentar o UNPIVOT e também ensinar as limitações destas cláusulas e mágicas, assim como explicar como fazer as mesmas coisas sem utilizar PIVOT e UNPIVOT.

Entendendo EXCEPT, INTERSECT e UNION do SQL

Atenção

Migramos este artigo para o novo site:

http://www.sqlfromhell.com/entendendo-except-intersect-e-union-do-sql/

Introdução ao SQL Server – Aula 2.2

Nesta segunda aula veremos como trabalhar com tipos de dados para armazenar Texto e Data.

Data/Hora

Armazenar data no banco de dados requer saber o intervalo de data/hora que se pretende armazenar e também a precisão desejada.

Os tipos DataTime, DataTime2 e SmallDataTime permitem armazenar datas e horas com intervalos específicos.

Datetime (8 bytes) de 1753-01-01 a 9999-12-31
Datetime2 (6-8 bytes) de 0001-01-01 a 9999-12-31
SmallDateTime (4 bytes) de 1900-01-01 a 2079-06-06

Já o tipo DateTimeOffset, permite armazenar datas e horas e também o UTC (fuso horário) da localidade em questão, permitindo cálculos precisos para sistemas que trabalham com mais de um fuso horário.

DateTimeOffset (8-10 bytes) 0001-01-01 a 9999-12-31

Para situações que se deseja somente armazenar data ou somente hora, existem os tipos Date e Time, reduzindo em alguns bytes o tamanho dos dados a serem armazenados.

Date (1-3 bytes) de 0001-01-01 a 9999-12-31
Time (3-5 bytes) de 00:00:00.0000000 a 23:59:59.9999999

Introdução ao SQL Server – Aula 2.1

Nesta segunda aula veremos como trabalhar com tipos de dados para armazenar Texto e Data.

Texto

Armazenar texto no banco de dados pode não ser uma tarefa fácil se não se sabe o tamanho dos textos que deseja armazenar e se não se sabe se o texto terá tamanho variável ou não.

O tipo de dados CHAR, permite armazenar textos com tamanho fixo, exemplo siglas como as de UF (AC, PR, SP, RJ…) e IATA (CWB, GRU, GIG…), de forma a utilizar 1 byte por caractere, podendo ter tamanho fixo para até 8000 caracteres.

Sintaxe: CHAR(n. de caracteres)

O tipo de dados VARCHAR, permite armazenar textos com tamanho variável, exemplo nomes e logradouros, de forma a utilizar 1 byte por caractere e 2 bytes para identificar o “final” do texto, podendo armazenar textos com mais de 8000 caracteres.

Sintaxe: VARCHAR(n. de caracteres), acima de 8000: VARCHAR(MAX)

Também há tipos de dados específicos para armazenar caracteres de outros idiomas, como árabe, hebraico, chinês e coreano, tendo um prefixo N, como NCHAR e NVARCHAR, utilizando 2 bytes para cada caractere, tendo assim tamanho fixo limitado à 4000 caracteres.

Sintaxe: NCHAR(n. de caracteres)

Sintaxe: NVARCHAR(n. de caracteres), acima de 4000: NVARCHAR(MAX)

Introdução ao SQL Server – Aula 1.2

Depois de definido quais as colunas que a tabela terá, se faz necessário definir os tipos de dados que serão utilizados para cada coluna, ex.:

1. Nome do cliente, precisar ser armazenado como “Texto”,
2. Número da rua como “Numérico”,
3. Preço da gasolina como “Numérico”,
4. Data de validade como “Data”…

Para isso, no SQL Server tem tipos de dados para cada uma destas situações:

1. Inteiro (-1, 0, 1, 2, 3, 4, …),
2. Decimal (149.99),
3. Flutuantes (π, 0.12515481…),
4. Monetário ($ 149.99),
5. Data/Hora (01/12/1987),
6. Booleano (Verdadeiro/Falso),
7. Texto

Obs.: Também existem outros tipos com finalidades bem específicas e complexas, como os tipos binários, XML, hierárquicos e geométricos, que não requerem atenção no momento.

Inteiros

Para o primeiro tipo numérico do SQL Server, se tem os tipos inteiros (bigint, int, smallint, tinyint), que possuem capacidade de armazenar números sem casas decimais, de diferentes intervalos.

O tinyint é capaz de armazenar números inteiros de 0 a 255, utilizando um 1 byte. Sendo suficientemente capaz de salvar o DDD de um telefone ou a idade de uma pessoa.

O smallint é capaz de armazenar números inteiros de -32,768 a 32,767, utilizando um 2 byte. Sendo suficientemente capaz de salvar a temperatura em ºC de uma cidade.

O int é capaz de armazenar números inteiros de -2,147,483,648 a 2,147,483,647, utilizando um 4 byte. Sendo suficientemente capaz de salvar um CEP sem os “-”.

O bigint é capaz de armazenar números inteiros de -9,223,372,036,854,775,808 a 9,223,372,036,854,775,807, utilizando um 8 byte. Sendo suficientemente capaz de salvar um CNPJ ou CPF.

Decimais

Para o segundo tipo numérico do SQL Server, se tem os tipos decimais (Decimal ou Numeric), que permitem armazenar números com casas decimais precisas, ou seja, o preço de uma casa 190.000,00, tem 8 dígitos, sendo 2 dígitos após a virgula, que será representado pelo tipo Decimal(8,2), segundo a sintaxe: Decimal(quantidade de dígitos, quantidade de dígitos após a virgula)

Esta precisão requer a utilização de um número um pouco maior de bytes em comparação aos outros tipos numéricos, conforme a relação:

1 a 9 dígitos, 5 bytes.
10-19 dígitos, 9 bytes.
20 a 28 dígitos, 13 bytes.
29 a 38 dígitos, 17 bytes.

Flutuantes

Para o terceiro tipo numérico do SQL Server, os tipos flutuantes (real e float) não requerem da mesma precisão dos tipos decimais, por consequência utilizam menos bytes (de 4 a 8 bytes), e podem armazenar números maiores que os decimais e inteiros, como dízimas periódicas e o valor de π, mas devido a sua falta de precisão, não são adequados para dados monetários.

Monetários

O quarto tipo numérico, existe especificamente para armazenar dados monetários utilizando menos bytes do que os tipos numéricos, conforme abaixo:

Money, para armazenar -922,337,203,685,477.5808 a 922,337,203,685,477.5807 em 8 bytes
SmallMoney, para armazenar – 214,748.3648 a 214,748.3647 em 4 bytes

Booleano

Um “quinto tipo numérico”, existe especificamente para armazenar dados do tipo 1:verdadeiro/0:falso denominado “bit” , utilizando 1 byte para cada 8 colunas deste tipo, ou seja, 1 coluna bit ocupa 1 byte na tabela, assim como 8 colunas bit ocupam também ocupam 1 byte, e 9 a 16 colunas ocupam 2 bytes, assim por diante.

Introdução ao SQL Server – Aula 1.1

Olá pessoas, faz um bom tempo que não escrevo nada no blog, mas achei interessante disponibilizar alguns assuntos que estou trabalhando em um treinamento de SQL realizado na EuroIT, sendo o primeiro deles um passo-a-passo sobre como criar/normalizar tabelas no SQL Server, que achei interessante compartilhar no blog, devido à abordagem mais prática adotada.

Para criar uma tabela em qualquer banco de dados, é necessário pensar como organizar os registros que serão inseridos nela, para não termos situações onde temos um monte de dados sem uma ordem adequada ou que permita identificar o que esta salvo lá, ex.:

João Batista Queiroz, Travessa da Lapa 5034, Joãozinho, 83792-000, 39382872A, 56877848454-54, 012.216.549-12, M, I, 2006-12-12, 1985-01-01

Para isso requer o uso de colunas para organizar estes dados, ex.:

Nome	Endereço	Apelido	…
João Batista Queiroz	Travessa da Lapa 5034	Joãozinho	…

Também é importante utilizar nomes que realmente especifiquem que dado esta armazenado em cada coluna e nas tabelas, ex.: Coluna Nome da tabela Cliente, para armazenar o nome do cliente.

Obs. 1: Em algumas empresas também se adotam nomenclaturas para as tabelas e as colunas, ex.: T0001_CLIENTE, C0001_NOME, N0001_DDD, N0001_TELEFONE …

Ao criar as colunas, se deve pensar também no trabalho que será necessário para organizar estes dados nestas colunas, como no caso os “endereços”.:

1. Que se pode não ser dividido, tendo toda a informação em uma coluna “endereço”

2. Que se pode dividir nas colunas: logradouro, número, bairro, complemento, CEP, cidade, estado, país…

3. Que se pode dividir em uma forma mais detalhada: tipo de logradouro (rua, avenida, praça…), prefixo do logradouro (coronel, marechal, tenente, presidente, são, santa, santo, novo…), logradouro (Teixeira Martins, José, Maria), sufixo do logradouro (I, II, III…)…

O problema do detalhamento para deixar os dados bem organizados, é o trabalho que será necessário para organizar estes dados em muitos colunas ao ponto de ficar muito complexo e requerer um esforço demasiado em comparação à forma que estes dados serão utilizados pelos usuário.

Obs. 2: Também se frisar a existência de limites de colunas para as tabelas, como 1024 colunas no SQL Server (ref. http://msdn.microsoft.com/en-US/library/ms143432.aspx)

Exemplo de Deadlock no SQL Server

Deadlock acontece quando dois ou mais processos são impedidos de prosseguir pois um estar bloqueando o outro, como exemplo, duas pessoas estão querendo usar o mesmo telefone para ligar para números diferentes, enquanto uma delas não ceder sua vez, nenhuma irá conseguir ligar.

No nível de transações de banco de dados não é muito diferente, teremos duas tabelas em nosso banco de dados:

CREATE TABLE Pagamentos (ID INT PRIMARY KEY)
GO
CREATE TABLE Contas (ID INT PRIMARY KEY)
GO

Agora, imagine que um cliente, o senhor 52, irá abrir uma transação para inserir um registro na tabela de pagamentos:

BEGIN TRAN
GO
INSERT INTO Pagamentos VALUES(1)
GO

E ao mesmo tempo, outro cliente, o senhor 55, irá inserir um registro na tabela de contas:

BEGIN TRAN
GO
INSERT INTO Contas VALUES(1)
GO

Até ai, tudo bem, as transações estão abertas, o senhor 55 e o senhor 52 estão utilizando tabelas totalmente diferentes. Mas o senhor 55 lembra que além de inserir um registro na tabela de contas, ele precisa inserir um registro na tabela de pagamentos na mesma transação, assim ele terá que esperar que o senhor 52 termine a transação dele primeiro.

INSERT INTO Pagamentos VALUES(1)
GO

Por enquanto nada crítico, é só um cliente esperando o outro terminar.

Mas o senhor 52 também inventa de querer inserir um registro na tabela de Contas:

INSERT INTO Contas VALUES(1)
GO

Dai acontece o Deadlock, o senhor 52 esperando o senhor 55 terminar a transação dele, e o senhor 55 esperando o senhor 52. Como se trata de uma Deadlock bem simples, o SQL Server vai rapidamente escolher qual dos dois clientes tem a preferência, e por consequência definirá sua vítima, que é o cliente que terá que morrer para que aquele que possui preferência consiga terminar seu processo.

E assim, no meu cenário, senhor 52 foi brutalmente assassinado (a transação sofre um ROLLBACK pelo próprio SQL Server, de forma que todos os rastros da vítima sejam apagados):

Msg 1205, Level 13, State 47, Line 4
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

E em homenagem a todos os processos que diariamente são vítimas de Deadlock:

|| EDIT 2012-03-29
|| Via: http://pessoalex.wordpress.com/2012/03/29/exemplo-de-deadlock-no-sql-server/