PIVOT and UNPIVOT
Já comentei no meu blog sobre como fazer PIVOT de linhas para colunas. Isso foi feito sem as cláusulas PIVOT, o que resulta em muito mais trabalho. Com essas “novas” cláusulas, podemos evitar esse trabalho adicional que antes se utilizava de CASE ou Multitable Inserts ou DECODE.
Para os nossos testes e exemplos usaremos o usuário OE e a tabela ORDERS no seu schema. Esta tabela consiste em dados a respeito de pedidos, como data(order_date), como foi feita a venda(order_mode) e o valor(order_total) entre outras informações. Vejam um SELECT simples com um GROUP BY nesta tabela.
Percebam que agrupamos pelo ano da venda além de onde foi feita, se foi uma venda direta, ou seja na loja, ou online pela internet. Assim mostramos o valor total das vendas com base nesses dois grupos. PIVOT faz uso de funções de agregação, apesar de não possuir a cláusula GROUP BY explícita, isso é gerado implicitamente.
Vamos fazer um PIVOT agora na coluna ORDER_MODE, de modo que a linha vire coluna. Para realizar um PIVOT devemos especificar alguns elementos nessa nova cláusula. O PIVOT_CLAUSE, PIVOT_FOR_CLAUSE e PIVOT_IN_CLAUSE. Vejam o descritivo dos elementos:
PIVOT_CLAUSE:
PIVOT_FOR_CLAUSE:
PIVOT_IN_CLAUSE:
O primeiro item é uma expressão de agregação, que será a SUM(order_total). Logo após temos a PIVOT_FOR_CLAUSE, na qual especificamos as colunas que irão fazer o PIVOT, que sera a order_mode, e para finalizar o PIVOT_IN_CLAUSE, no qual podemos filtrar os valores dessa linha ou colocar ANY para todos os valores. Vejamos um exemplo agora.
Podemos ver que fizemos o PIVOT na coluna order_mode, que tinha valores direct ou online em todas as linhas. Agora ambos os valores passaram a ser colunas, juntamente com o a função de agregação para cada ano. Percebam que utilizei o exemplo com WITH pois se tivesse feito sem isso eu não poderia referencia as colunas que são usadas em PIVOT_CLAUSE e PIVOT_FOR_CLAUSE pois seria lançado um erro de identificador inválido.
Vamos criar uma tabela agora para armazenar o resultado da query anterior para o nosso próximo exemplo, o UNPIVOT.
Veja que coloquei alias nas colunas na cláusula PIVOT_IN_CLAUSE. Fiz isto para o nome das colunas não tem aspas simples e também para evitar a palavra reservar “online”, então coloquei com dois “e” no final Agora vamos fazer o UNPIVOT.
Percebam que o resultado foi o mesmo que o anterior da primeira imagem, isso nem sempre será verdadeiro, pois pode ser necessárias mais informações para se reconstruir novamente o estado anterior. Veja a cláusula do UNPIVOT
Lembrando que podemos excluir os resultados dos NULLs ou não. Podemos fazer essas operações em mais de uma coluna e/ou com mais de uma função de agregação. Podemos criar alias tanto para o IN_CLAUSE como nas funções de agregação. Os nomes das colunas que sofrem o pivot obedecem a seguinte regra com base nos alias:
|
Column Aliased? |
In-Value Aliased? |
Pivot Column Name |
|
N |
N |
pivot_in_clause value |
|
Y |
Y |
pivot_in_clause alias || ‘_’ || pivot_clause alias |
|
N |
Y |
pivot_in_clause alias |
|
Y |
N |
pivot_in_clause value || ‘_’ || pivot_clause alias |
Referência
Abraço
