oracle

 

Fazer um “pivot” permite reestruturar dados de uma estrutura de dados tipicamente normalizada para um formato diferente, e assim formatar as informações que você precisa de uma maneira muito mais legível aos olhos do usuário que requisita as informações.

As cláusulas PIVOT e UNPIVOT fazem parte das novas features da versão 11g. Nas versões anteriores era necessário uma codificação mais elaborada e complicda, usando CASE. Agora, além de facilitar na codificação, esta operação foi otimizada (segundo a documentação oficial do Oracle) – e assim a operação ganhou uma boa melhora de desempenho em relação a codificação com CASE, considerando obviamente SELECT’s equivalentes.

PIVOT

Permite “nivelar” uma tabela normalizada. Muito útil para formatar visualmente os dados para o seu aplicativo ou para um relatório. A melhor maneira de entender tanto o funcionamento do PIVOT quanto o seu resultado é mostrar um exemplo.
Vamos imaginar que temos uma tabela de VENDAS, em uma empresa que distribui chocolates – e nós queremos relatórios sumarizados, mostrando totais de cada chocolate em colunas separadas, e também agrupando por cliente.
Vamos ao que interessa!

CREATE TABLE vendas (
  id            NUMBER,
  cliente   NUMBER,
  produto  VARCHAR2(20),
  qtde      NUMBER
);

Tabela criada, agora vamos popular a tabela:

INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (1,10,'Charge',50);
INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (2,11,'Bis',40);
INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (3,12,'Charge',80);
INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (4,13,'Toblerone',30);
INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (5,10,'Toblerone',40);
INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (6,11,'Alpino',100);
INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (7,13,'Bis',200);
INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (8,12,'Alpino',80);
INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (9,10,'Charge',30);
INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (10,11,'Charge',20);
COMMIT;
SQL> SELECT * from vendas;

ID    CLIENTE PRODUTO                    QTDE
 ---------- ---------- -------------------- ----------
 1         10 Charge                       50
 2         11 Bis                          40
 3         12 Charge                       80
 4         13 Toblerone                    30
 5         10 Toblerone                    40
 6         11 Alpino                      100
 7         13 Bis                         200
 8         12 Alpino                       80
 9         10 Charge                       30
 10         11 Charge                       20

10 linhas selecionadas.

Agora vamos usar o PIVOT para mostrar o total vendido de cada produto em apenas uma linha, com um produto por coluna.

SQL> SELECT *
  2  FROM   (SELECT produto, qtde
  3          FROM   vendas)
  4  PIVOT  (SUM(qtde) AS Total FOR (PRODUTO)
            IN ('Charge', 'Bis', 'Toblerone', 'Alpino'));

'Charge'_TOTAL 'Bis'_TOTAL 'Toblerone'_TOTAL 'Alpino'_TOTAL
-------------- ----------- ----------------- --------------
           180         240                70            180

Agora, agrupando por cliente:

SQL> SELECT *
  2  FROM   (SELECT cliente, produto, qtde
  3          FROM   vendas)
  4  PIVOT  (SUM(qtde) AS Total FOR (PRODUTO)
                    IN ('Charge', 'Bis', 'Toblerone', 'Alpino'))
  5  ORDER BY cliente;

   CLIENTE 'Charge'_TOTAL 'Bis'_TOTAL 'Toblerone'_TOTAL 'Alpino'_TOTAL
---------- -------------- ----------- ----------------- --------------
        10             80                            40
        11             20          40                              100
        12             80                                           80
        13                        200                30

UNPIVOT

A cláusula UNPIVOT faz o caminho inverso: pega uma tabela “pivoteada” e normaliza. Como exemplo, vou usar os mesmos dados para criar uma nova tabela.

SQL> create table vendas_2 as
  2  SELECT *
  3  FROM   (SELECT cliente, produto, qtde
  4          FROM   vendas)
  5  PIVOT  (SUM(qtde) AS Total FOR (PRODUTO)
  6          IN ('Charge', 'Bis', 'Toblerone', 'Alpino'))
  7  ORDER BY cliente;

Tabela criada.
SQL> select * from vendas_2;

CLIENTE    'Charge'_TOTAL 'Bis'_TOTAL 'Toblerone'_TOTAL 'Alpino'_TOTAL
---------- -------------- ----------- ----------------- --------------
10         80                         40
11         20             40                            100
12         80                                           80
13                        200         30

E finalmente o uso do UNPIVOT!

SQL> SELECT *
  2  FROM   vendas_2
  3  UNPIVOT (qtde FOR produto IN ("'Charge'_TOTAL" AS 'Charge',
  4                                "'Bis'_TOTAL" AS 'Bis',
  5                                "'Toblerone'_TOTAL" AS 'Toblerone',
  6                                "'Alpino'_TOTAL" AS 'Alpino'));

   CLIENTE PRODUTO         QTDE
---------- --------- ----------
        10 Charge            80
        10 Toblerone         40
        11 Charge            20
        11 Bis               40
        11 Alpino           100
        12 Charge            80
        12 Alpino            80
        13 Bis              200
        13 Toblerone         30

9 linhas selecionadas.

Opa! Peraí Milton, mas no primeiro exemplo eram 10 registros. E agora ficaram apenas 9 registros. Por que?

Muito simples. Reparem que no conjunto inicial de registros, o mesmo cliente (10) comprou o mesmo produto (Charge) por duas vezes. Após agrupar os registros em outra tabela, como fizemos na vendas_2, fica impossível o Oracle adivinhar que essa quantidade veio de 2 registros diferentes. Na prática, ou melhor dizendo, em um sistema do mundo real, esta tabela teria mais algum campo para diferenciar essas vendas (um campo com a data da venda, por exemplo), e caso o agrupamento fosse feito por este campo, aí sim seria possível a quebra mais detalhada dos registros.