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>;
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
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
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
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
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.