Pular para o conteúdo

Aprenda como fazer PIVOT and UNPIVOT para facilitar suas consultas de dados !

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.

PIVOT and UNPIVOT

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:

2.JPG

PIVOT_FOR_CLAUSE:

3.JPG

PIVOT_IN_CLAUSE:

4.JPG

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.

1.JPG

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.

2.JPG

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.

3.JPG

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

un1.JPG
un2.JPG

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

Quão útil foi este post ?

Clique em uma estrela para classificar o post

nota média 5 / 5. Contagem de votos: 38

Sem votos ! Seja o primeiro a classificar !

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

plugins premium WordPress