Artigos Banco de Dados Utilizando os operadores PIVOT e UNPIVOT no SQL Server

Veja neste artigo como usar os operadores relacionais PIVOT e UNPIVOT para alterar uma expressão com valor de tabela em outra tabela. O PIVOT pode ser usado para transformar os valores exclusivos (os valores não podem se repetir) de uma coluna, ou seja, linhas, em colunas múltiplas na saída, além de executar agregações onde forem necessárias em quaisquer valores de coluna remanescentes que sejam desejados na saída final. UNPIVOT executa a operação oposta a PIVOT, transformando em linhas. Veja a sintaxe do PIVOT na listagem 1.


SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>) 
AS <alias for the source query>
PIVOT
(<aggregation function>(<column being aggregated>)
FOR 
[<column that contains the values that will become column headers>] 
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
Listagem 1. Sintaxe do PIVOT

Primeiro passo

Criar a tabela tb_empresas, adicionar os dados e exibí-los conforme a listagem 2, que servirá para nosso exemplo didático.


CREATE TABLE [dbo].[tb_empresas](
	[data] [date] NULL,
	[nome] [varchar](100) NULL,
	[valor] [decimal](18, 2) NULL
) ON [PRIMARY]

  INSERT INTO tb_empresas (data, nome, valor)
  VALUES  (convert(datetime,'2012-01-01',103),  'EMPRESA1',   100)
  INSERT INTO tb_empresas (data, nome, valor)
  VALUES  (convert(datetime,'2012-02-02',103),  'EMPRESA2',   200)
  INSERT INTO tb_empresas (data, nome, valor)
  VALUES  (convert(datetime,'2012-03-03',103),  'EMPRESA3',   300)
  INSERT INTO tb_empresas (data, nome, valor)
  VALUES  (convert(datetime,'2012-04-04',103),  'EMPRESA4',   400)
  INSERT INTO tb_empresas (data, nome, valor)
  VALUES  (convert(datetime,'2012-05-05',103),  'EMPRESA5',   500)
  INSERT INTO tb_empresas (data, nome, valor)
  VALUES  (convert(datetime,'2012-06-06',103),  'EMPRESA6',   600)
  INSERT INTO tb_empresas (data, nome, valor)
  VALUES  (convert(datetime,'2012-07-07',103),  'EMPRESA7',   700)
  INSERT INTO tb_empresas (data, nome, valor)
  VALUES  (convert(datetime,'2012-08-08',103),  'EMPRESA8',   800)
  INSERT INTO tb_empresas (data, nome, valor)
  VALUES  (convert(datetime,'2012-09-09',103),  'EMPRESA9',   900)
  INSERT INTO tb_empresas (data, nome, valor)
  VALUES  (convert(datetime,'2012-10-10',103),  'EMPRESA10',   1000)

  
 select data, nome, valor  from tb_empresas
Listagem 2. Criar tabela, adicionar os dados e exibi-los

Será obtido o resultado abaixo:

DATA NOME VALOR
2012-01-01 EMPRESA1 100,00
2102-02-02 EMPRESA2 200,00
2012-03-03 EMPRESA3 300,00
2012-04-04 EMPRESA4 400,00
2012-05-05 EMPRESA5 500,00
2012-06-06 EMPRESA6 600,00
2012-07-07 EMPRESA7 700,00
2012-08-08 EMPRESA8 800,00
2012-09-09 EMPRESA9 900,00
2012-10-10 EMPRESA10 1000,00

Segundo passo

Criar o procedure OBTER_COLUNAS_DINAMICAMENTE, que irá transformar todas as linhas, independente da quantidade, em colunas, conforme a listagem 3.


create procedure [dbo].[OBTER_COLUNAS_DINAMICAMENTE]
AS
SET NOCOUNT ON

DECLARE @COLUNAS VARCHAR(MAX)
SET @COLUNAS = ''

SELECT @COLUNAS = COALESCE(@COLUNAS + '[' + 
(CAST(NOME AS NVARCHAR(255))) + '],','')
FROM (SELECT DISTINCT NOME FROM tb_empresas) AS DADOS_HORIZONTAIS
SET @COLUNAS = LEFT (@COLUNAS, LEN(@COLUNAS)-1)

DECLARE @SQLSTRING NVARCHAR(500);

SET @SQLSTRING = N'
SELECT * FROM(SELECT  DATEPART(MONTH, DATA) AS MES, NOME, 
VALOR FROM tb_empresas) AS DADOS_HORIZONTAIS
PIVOT(
 MAX(VALOR) FOR NOME IN('+@COLUNAS+')) AS PivotTable;'

EXECUTE SP_EXECUTESQL @SQLSTRING
Listagem 3. Procedure OBTER_COLUNAS_DINAMICAMENTE

Terceiro passo

Executar o procedure OBTER_COLUNAS_DINAMICAMENTE e observar que todas as linhas do campo nome (EMPRESA1, EMPRESA2, EMPRESA3, EMPRESA4, EMPRESA5, EMPRESA6, EMPRESA7, EMPRESA8, EMPRESA9 e EMPRESA10) foram transformadas em colunas.

Isso pode ser feito executando o comando EXEC OBTER_COLUNAS_DINAMICAMENTE.

Deverá obter o resultado abaixo:

MÊS EMPRESA1 EMPRESA10 EMPRESA2 EMPRESA3 EMPRESA4 EMPRESA5 EMPRESA6 EMPRESA7 EMPRESA8 EMPRESA9
1 100.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 NULL NULL 200.00 NULL NULL NULL NULL NULL NULL NULL
3 NULL NULL NULL 300.00 NULL NULL NULL NULL NULL NULL
4 NULL NULL NULL NULL 400.00 NULL NULL NULL NULL NULL
5 NULL NULL NULL NULL NULL 500.00 NULL NULL NULL NULL
6 NULL NULL NULL NULL NULL NULL 600.00 NULL NULL NULL
7 NULL NULL NULL NULL NULL NULL NULL 700.00 NULL NULL
8 NULL NULL NULL NULL NULL NULL NULL NULL 800.00 NULL
9 NULL NULL NULL NULL NULL NULL NULL NULL NULL 900.00
10 NULL 1000.00 NULL NULL NULL NULL NULL NULL NULL NULL

O UNPIVOT executa praticamente a operação inversa de PIVOT, transformando colunas em linhas. Considerando a tabela criada na listagem 4 e que você precisa transformar os identificadores de coluna Empresa1, Empresa2, Empresa3, Empresa4, Empresa5, Empresa6, Empresa7, Empresa8, Empresa9 e Empresa10 em valores de linhas. Isso significa que você deve identificar duas colunas adicionais. A coluna que conterá os valores de coluna que você está transformando (Empresa1, Empresa2, Empresa3,...) será chamada de Nome_empresa e a coluna que conterá os valores que atualmente residem nas colunas que estão sendo transformadas será chamada de valor_cartao, conforme o código da listagem 5. Essas colunas correspondem, respectivamente, a pivot_column e value_column na definição Transact-SQL. 


CREATE TABLE tb_empresas2 (n_cartao_refeicao int, Empresa1 int, Empresa2 int,   
Empresa3 int, Empresa4 int, Empresa5 int, Empresa6 int, Empresa7 int, 
Empresa8 int, Empresa9 int, Empresa10 int);

INSERT INTO tb_empresas2 VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
INSERT INTO tb_empresas2 VALUES (10, 20, 30, 40, 50, 60, 70, 80, 90, 
100, 110);
INSERT INTO tb_empresas2 VALUES (100, 200, 300, 400, 500, 600, 700, 
800, 900, 1000, 1100);
INSERT INTO tb_empresas2 VALUES (1000, 2000, 3000, 4000, 5000, 6000, 
7000, 8000, 9000, 1000, 11000);
INSERT INTO tb_empresas2 VALUES (101, 202, 303, 404, 505, 606, 707, 
808, 909, 1010, 11011);

SELECT n_cartao_refeicao, Empresa1, Empresa2, Empresa3, Empresa4, Empresa5, 
Empresa6, Empresa7, Empresa8, Empresa9, Empresa10
FROM tb_empresas2
Listagem 4. Criar tabela, adicionar os dados e exibi-los

Será obtido o resultado abaixo:

n_cartao_refeicao Empresa1 Empresa2 Empresa3 Empresa4 Empresa5 Empresa6 Empresa7 Empresa8 Empresa9 Empresa10
1 2 3 4 5 6 7 8 9 10 11
10 20 30 40 50 60 70 80 90 100 110
100 200 300 400 500 600 700 800 900 1000 1100
1000 2000 3000 4000 5000 6000 7000 8000 9000 1000 11000
101 202 303 404 505 606 707 808 909 1010 11011

Depois realize a consulta que está na listagem 5 para transformar todas colunas (Empresa1, Empresa2..., etc) em linhas. 


SELECT n_cartao_refeicao, Nome_empresa, valor_cartao
FROM 
   (SELECT n_cartao_refeicao, Empresa1, Empresa2, Empresa3, Empresa4, 
   Empresa5, Empresa6, Empresa7, Empresa8, Empresa9, Empresa10
   FROM tb_empresas2) p
UNPIVOT
   (valor_cartao FOR Nome_empresa IN 
      (Empresa1, Empresa2, Empresa3, Empresa4, Empresa5, Empresa6, 
      Empresa7, Empresa8, Empresa9, Empresa10)
)AS UNPIVOT_tb_empresas2
Listagem 5. Consulta UNPIVOT transforma colunas em linhas

Será obtido o resultado abaixo:

n_cartao_refeicao Nome_empresa valor_cartao
1 Empresa1 2
1 Empresa2 3
1 Empresa3 4
1 Empresa4 5
1 Empresa5 6
1 Empresa6 7
1 Empresa7 8
1 Empresa8 9
1 Empresa9 10
1 Empresa10 11
10 Empresa1 20
10 Empresa2 30
10 Empresa3 40
10 Empresa4 50
10 Empresa5 60
10 Empresa6 70
10 Empresa7 80
10 Empresa8 90
10 Empresa9 100
10 Empresa10 110
100 Empresa1 200
100 Empresa2 300
100 Empresa3 400
100 Empresa4 500
100 Empresa5 600
100 Empresa6 700
100 Empresa7 800
100 Empresa8 900
100 Empresa9 1000
100 Empresa10 1100
1000 Empresa1 2000
1000 Empresa2 3000
1000 Empresa3 4000
1000 Empresa4 5000
1000 Empresa5 6000
1000 Empresa6 7000
1000 Empresa7 8000
1000 Empresa8 9000
1000 Empresa9 1000
1000 Empresa10 11000
101 Empresa1 202
101 Empresa2 303
101 Empresa3 404
101 Empresa4 505
101 Empresa5 606
101 Empresa6 707
101 Empresa7 808
101 Empresa8 909
101 Empresa9 1010
101 Empresa10 11011

O objetivo deste artigo foi apresentar, de forma prática, os operadores PIVOT e UNPIVOT do SQL Server. Com base no que foi mostrado aqui, é possível adaptar de acordo com cada necessidade.