PIVOT e UNPIVOT - Converter linha x coluna

PIVOT e UNPIVOT

Olá pessoal, no post de hoje mostro um pequeno tutorial de como transformar linhas, resultado de uma consulta, em colunas, utilizando a função PIVOT e também o resultado inverso, utilizando UNPIVOT.

A ideia aqui é tentar converter os dados de várias linhas, agrega-las e converte-las em colunas.

1. 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 e nós queremos relatórios sumarizados, mostrando totais de cada item em colunas separadas.
   Vamos ao que interessa!
   CREATE TABLE vendas (
     id            NUMBER(2),
     cliente   NUMBER(2),
     produto  VARCHAR2(20),
     qtde      NUMBER(2)
   );
   
   Tabela criada, agora vamos popular a tabela:
   INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (1,10,'Dart',50);

   INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (2,11,'Opala',40);

   INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (3,12,'Dart',80);

   INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (4,13,'Civic',30);

   INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (5,10,'Civic',40);

   INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (6,11,'Fusca',100);

   INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (7,13,'Opala',200);

   INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (8,12,'Fusca',80);

   INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (9,10,'Dart',30);

   INSERT INTO VENDAS (ID,CLIENTE,PRODUTO,QTDE) values (10,11,'Dart',20);

   COMMIT;

   SQL> SELECT * from vendas;

   
   

   ID    CLIENTE PRODUTO                    QTDE

    ---------- ---------- -------------------- ----------

    1         10 Dart                       50

    2         11 Opala                          40

    3         12 Dart                       80

    4         13 Civic                    30

    5         10 Civic                    40

    6         11 Fusca                      100

    7         13 Opala                         200

    8         12 Fusca                       80

    9         10 Dart                       30

    10         11 Dart                       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 ('Dart', 'Opala', 'Civic', 'Fusca'));

   
   

   'Dart'_TOTAL 'Opala'_TOTAL 'Civic'_TOTAL 'Fusca'_TOTAL

   -------------- ----------- ----------------- --------------

              180         240                70            180

   
   Agora, agrupando por cliente, mostra o resultado de uma nova forma:

   SQL> SELECT *

     2  FROM   (SELECT cliente, produto, qtde

     3          FROM   vendas)

     4  PIVOT  (SUM(qtde) AS Total FOR (PRODUTO)

                       IN ('Dart', 'Opala', 'Civic', 'Fusca'))

     5  ORDER BY cliente;

   
   

      CLIENTE 'Dart'_TOTAL 'Opala'_TOTAL 'Civic'_TOTAL 'Fusca'_TOTAL

   ---------- -------------- ----------- ----------------- --------------

           10             80                            40

           11             20          40                              100

           12             80                                           80

           13                        200                30

   
   

   
   Adicionando a palavra-chave XML para o operador PIVOT nos permite converter os resultados gerados para o formato XML. Ele também faz o comando PIVOT um pouco mais flexível, permitindo-nos substituir a clausula IN com a subquery, pelo coringa ANY.
   SQL> SELECT *

   
   

   FROM   (SELECT produto, qtde

               FROM   vendas)

   PIVOT XML (SUM(qtde) AS Total FOR (produto)

                   IN (ANY));

   
   PRODUTO_XML

   -----------------------------------------------------------------------------------------------------------------------------------------------------------

   <PivotSet><item><column name = "PRODUTO">Dart</column><column name = "TOTAL">180</column></item><item><column name = "PRODUTO">Fusca</column><column nam

   e = "TOTAL">180</column></item><item><column name = "PRODUTO">Civic</column><column name = "TOTAL">70</column></item><item><column name = "PRODUTO">Opala<

   /column><column name = "TOTAL">240</column></item></PivotSet>

   
   

   
   
2. 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 ('Dart', 'Opala', 'Civic', 'Fusca'))
     7  ORDER BY cliente;
   
   
   Tabela criada.
   SQL> select * from vendas_2;
   
   
   CLIENTE    'Dart'_TOTAL 'Opala'_TOTAL 'Civic'_TOTAL 'Fusca'_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 ("'Dart'_TOTAL" AS 'Charger',
     4                                "'Opala'_TOTAL" AS 'Opala',
     5                                "'Civic'_TOTAL" AS 'Fusion',
     6                                "'Fusca'_TOTAL" AS 'Fusca'));
   
   
      CLIENTE PRODUTO         QTDE
   ---------- --------- ----------
           10 Dart            80
           10 Civic         40
           11 Dart            20
           11 Opala               40
           11 Fusca           100
           12 Dart            80
           12 Fusca            80
           13 Opala              200
           13 Civic         30
   
   
   9 linhas selecionadas.

É isso pessoal, espero ter ajudado e que possa ser útil. Boa sorte a todos e até um próximo post.