SQLServerLogo

Os bancos de dados relacionais representam, sem sombra de dúvidas, um mecanismo de fundamental importância nas organizações. Projetados para o armazenamento de grandes volumes de informações num formato estruturado, estes repositórios fornecem meios para a condução das operações cotidianas nos mais variados segmentos. A partir de bases relacionais é possível também a geração de diversas análises sob a forma de relatórios, sendo este um importante instrumento no controle e gerenciamento das atividades dentro de uma companhia.

A construção de relatórios implica muitas vezes na necessidade de transformar os dados provenientes de tabelas relacionais, viabilizando com isto a obtenção de informações que atendam a necessidades específicas de um grupo de usuários. Um tipo de modificação comum neste sentido consiste em se inverter a disposição de linhas e colunas obtidas através do retorno de uma query: na prática acontecerá a geração de novas colunas a partir de um campo que corresponda a uma chave, com o agrupamento de valores levando em consideração este fator.

Considerando especificamente o SQL Server, este gerenciador de banco de dados disponibiliza o operador PIVOT para transformações desse gênero. O objetivo deste post é justamente demonstrar como o comando PIVOT pode ser utilizado na conversão de linhas em colunas, auxiliando assim desenvolvedores que façam uso deste produto da Microsoft no dia-a-dia.

Exemplo de utilização do operador PIVOT

O exemplo apresentado a seguir fará uso de uma tabela chamada “CotacoesPorDataMoeda”. Essa estrutura armazenará as os valores das cotações de moedas estrangeiras (dólar norte-americano, euro e libra esterlina) numa determinada data. Na Listagem 1 está o script para a criação e inclusão de informações na tabela CotacoesPorDataMoeda.

CREATE TABLE [dbo].[CotacoesPorDataMoeda](
	[DataCotacao] [date] NOT NULL,
	[CodMoeda] [varchar](3) NOT NULL,
	[ValorCotacao] [numeric](18, 4) NOT NULL,
	PRIMARY KEY ([DataCotacao], [CodMoeda])
)
GO

INSERT INTO dbo.CotacoesPorDataMoeda(DataCotacao, CodMoeda, ValorCotacao)
VALUES('2014-04-17', 'USD', 2.2357)

INSERT INTO dbo.CotacoesPorDataMoeda(DataCotacao, CodMoeda, ValorCotacao)
VALUES('2014-04-16', 'USD', 2.2418)

INSERT INTO dbo.CotacoesPorDataMoeda(DataCotacao, CodMoeda, ValorCotacao)
VALUES('2014-04-15', 'USD', 2.2385)

INSERT INTO dbo.CotacoesPorDataMoeda(DataCotacao, CodMoeda, ValorCotacao)
VALUES('2014-04-14', 'USD', 2.2147)

INSERT INTO dbo.CotacoesPorDataMoeda(DataCotacao, CodMoeda, ValorCotacao)
VALUES('2014-04-17', 'EUR', 3.0927)

INSERT INTO dbo.CotacoesPorDataMoeda(DataCotacao, CodMoeda, ValorCotacao)
VALUES('2014-04-16', 'EUR', 3.1012)

INSERT INTO dbo.CotacoesPorDataMoeda(DataCotacao, CodMoeda, ValorCotacao)
VALUES('2014-04-15', 'EUR', 3.0874)

INSERT INTO dbo.CotacoesPorDataMoeda(DataCotacao, CodMoeda, ValorCotacao)
VALUES('2014-04-14', 'EUR', 3.0616)

INSERT INTO dbo.CotacoesPorDataMoeda(DataCotacao, CodMoeda, ValorCotacao)
VALUES('2014-04-17', 'LIB', 3.7593)

INSERT INTO dbo.CotacoesPorDataMoeda(DataCotacao, CodMoeda, ValorCotacao)
VALUES('2014-04-16', 'LIB', 3.7708)

INSERT INTO dbo.CotacoesPorDataMoeda(DataCotacao, CodMoeda, ValorCotacao)
VALUES('2014-04-15', 'LIB', 3.7400)

INSERT INTO dbo.CotacoesPorDataMoeda(DataCotacao, CodMoeda, ValorCotacao)
VALUES('2014-04-14', 'LIB', 3.7048)

GO

Listagem 1: Scripts para criação e preenchimento da tabelaCotacoesPorDataMoeda

Executando uma consulta como a que consta na Listagem 2 será possível observar as diferentes cotações, com tais dados estando agrupados por data e código da moeda (Imagem 1).

SELECT DataCotacao, CodMoeda, ValorCotacao
FROM dbo.CotacoesPorDataMoeda

Listagem 2: Efetuando uma consulta à tabela CotacoesPorDataMoeda

Imagem01
Imagem 1. Resultado de uma consulta à tabela CotacoesPorDataMoeda

O uso do operador PIVOT permitirá reagrupar estas informações, com a geração de colunas para cada tipo de moeda. Esses campos seriam então preenchidos com valores correspondentes à cotação numa determinada data. A Listagem 3 apresenta o código que possibilitará tal transformação; já na Imagem 2 está o resultado da execução desta expressão SQL.

Conforme é possível observar, a utilização do operador PIVOT envolve:

  • Um Sub-SELECT, com os dados que deverão ser transformados (no caso, um SELECT simples consultando dados da tabela CotacoesPorDataMoeda);
  • Uma seção formada pelo comando PIVOT, em que constará primeiramente uma expressão de agrupamento (para este exemplo foi utilizada a função AVG para obtenção da cotação média, mas também seria possível o uso de SUM, MAX, MIN). Além disso, nota-se a presença da cláusula FOR com o nome da coluna a ser transformado (no caso o campo CodMoeda), assim como os valores desta última que servirão de base para a geração de novas colunas (para campos do tipo VARCHAR, não utilizar aspas simples; uma prática comum é representar os valores entre colchetes, de forma a permitir a geração de colunas cujo nome também possa conter espaços);
  • Por fim, no SELECT principal desta instrução SQL estão os campos de data de cotação, bem como as novas colunas representando os valores de moedas transformados por meio do operador PIVOT (estas três colunas foram renomeadas através aliases indicados pela cláusula AS).
SELECT DataCotacao,
       [USD] AS VlDolar,
       [EUR] AS VlEuro,
       [LIB] AS VlLibra
FROM
(
    SELECT DataCotacao, CodMoeda, ValorCotacao
    FROM dbo.CotacoesPorDataMoeda
) C
PIVOT
(
    AVG(ValorCotacao)
    FOR CodMoeda IN ([USD], [EUR], [LIB])
) AS P

Listagem 3: Exemplo de utilização do operador PIVOT

Imagem02
Imagem 2.
Resultado de uma consulta à tabela CotacoesPorDataMoeda

Com isto encerro mais um artigo. Procurei demonstrar aqui de que forma o operador PIVOT pode ser útil em transformações de dados a partir do SQL Server. Em um futuro post discutirei o processo inverso, empregando para isto o operador UNPIVOT na conversão de colunas em linhas.

Espero que este conteúdo possa lhe ser útil em algum momento. Até uma próxima oportunidade!

Links

The PIVOT Operator
http://blogs.msdn.com/b/craigfr/archive/2007/07/03/the-pivot-operator.aspx

 

Renato Groffe

Atua como consultor em atividades voltadas ao desenvolvimento de softwares há mais de 13 anos. Bacharel em Sistemas de Informação, com especialização em Engenharia de Software. Microsoft Certified Technology Specialist (Web, WCF, Distributed Applications, ADO.NET, Windows Forms), Microsoft Specialist (HTML5 with JavaScript and CSS3, Developing ASP.NET MVC 4 Web Applications), Oracle Certified Associate (PL/SQL), Sun Certified (SCJP, SCWCD), ITIL Foundation V2, Cobit 4.1 Foundation.

Facebook Google+ 

Comentários

comentarios