Este post mostra como usar os operadores PIVOT e UNPIVOT no Oracle Database a partir da versão 11g.
Como sabemos, as tabelas relacionais são apresentadas em pares de coluna e valor.
Vamos tomar por exemplo uma tabela chamada CUSTOMERS.
SQL> desc sh.customers
Name Type Nullable Default Comments
———————- ———— ——– —————- ————————————————–
CUST_ID NUMBER primary key
CUST_FIRST_NAME VARCHAR2(20) first name of the customer
CUST_LAST_NAME VARCHAR2(40) last name of the customer
CUST_GENDER CHAR(1) Y gender; low cardinality attribute
CUST_YEAR_OF_BIRTH NUMBER(4) Y customer year of birth
CUST_MARITAL_STATUS VARCHAR2(20) Y customer marital status; low cardinality attribute
CUST_STREET_ADDRESS VARCHAR2(40) customer street address
CUST_POSTAL_CODE VARCHAR2(10) postal code of the customer
CUST_CITY VARCHAR2(30) city where the customer lives
CUST_STATE_PROVINCE VARCHAR2(40) Y customer geography: state or province
COUNTRY_ID CHAR(2) foreign key to the countries table (snowflake)
CUST_MAIN_PHONE_NUMBER VARCHAR2(25) Y customer main phone number
CUST_INCOME_LEVEL VARCHAR2(30) Y customer income level
CUST_CREDIT_LIMIT NUMBER Y customer credit limit
CUST_EMAIL VARCHAR2(30) Y customer email id
CUST_TOTAL VARCHAR2(14) Y ‘Customer total’
Ao fazer um select nesta tabela temos o resultado abaixo:
SQL> select t.cust_id,t.country_id,t.cust_income_level from sh.customers t;
CUST_ID COUNTRY_ID CUST_INCOME_LEVEL
———- ———- ——————————
1550 US I: 170,000 – 189,999
1560 US C: 50,000 – 69,999
1570 US H: 150,000 – 169,999
1590 US C: 50,000 – 69,999
1600 UK C: 50,000 – 69,999
1610 US I: 170,000 – 189,999
1620 NL F: 110,000 – 129,999
1630 DE B: 30,000 – 49,999
1640 NL E: 90,000 – 109,999
1650 NL F: 110,000 – 129,999
1660 DE H: 150,000 – 169,999
1670 NL D: 70,000 – 89,999
1680 NL F: 110,000 – 129,999
1690 NL F: 110,000 – 129,999
1700 US E: 90,000 – 109,999
1710 UK E: 90,000 – 109,999
1720 UK H: 150,000 – 169,999
1730 US C: 50,000 – 69,999
1740 US C: 50,000 – 69,999
1750 UK F: 110,000 – 129,999
Observe que os dados são representados como linhas de valores: para cada cliente,
o registro mostra seu pais de origem e a faixa de renda a que esse cliente pertence.
Abaixo veremos como poderia se obter um relatório da frequência de compras por pais, por faixa de renda,isto é, quantos clientes compraram algo só uma vez, duas vezes, três vezes, etc.
Em uma instrução SQL comum, executamos a seguinte instrução:
SQL> select t.country_id,t.cust_income_level, count(1) from sh.customers t
2 group by t.country_id,t.cust_income_level;
COUNTRY_ID CUST_INCOME_LEVEL COUNT(1)
———- —————————— ———-
AR A: Below 30,000 20
AR B: 30,000 – 49,999 12
AR C: 50,000 – 69,999 24
AR D: 70,000 – 89,999 23
AR E: 90,000 – 109,999 35
AR F: 110,000 – 129,999 61
AR G: 130,000 – 149,999 22
AR H: 150,000 – 169,999 22
AR I: 170,000 – 189,999 24
AR J: 190,000 – 249,999 10
AU A: Below 30,000 66
AU B: 30,000 – 49,999 33
AU C: 50,000 – 69,999 64
AU D: 70,000 – 89,999 67
AU E: 90,000 – 109,999 100
AU F: 110,000 – 129,999 204
AU G: 130,000 – 149,999 69
AU H: 150,000 – 169,999 67
AU I: 170,000 – 189,999 65
AU J: 190,000 – 249,999 32
Essas são as informações que você precisa, mas seu formato torna a leitura difícil.
Uma melhor maneira de apresentar os mesmos dados pode ser mediante relatórios com referências cruzadas, nos quais os dados podem ser organizados verticalmente e a lista de estados, horizontalmente, como em uma folha de cálculo.
1 – OPERADOR PIVOT
Antes do surgimento do Oracle Database 11 g, para realizar essa ação era necessário usar algum tipo de função de decodificação para cada valor e escrever cada valor como coluna independente.
Felizmente, agora há uma nova e útil característica, PIVOT, para reordenar dados e apresentar os resultados das consultas como tabela de referências cruzadas, com o operador pivot.
Na consulta abaixo apresentaremos a baseada na query executada acima um exemplo a praticidade do operador pivot.
SQL> select * from (
2 select t.country_id,t.cust_income_level
3 from sh.customers t
4 )
5 pivot
6 (
7 count(country_id)
8 for country_id in (‘AR’,’BR’,’JP’,’FR’,’NZ’,’AU’,’ES’)
9 )
10 order by cust_income_level
11 ;
Os valores de country_id são apresentados na linha de cabeçalho, e não em uma coluna.
CUST_INCOME_LEVEL ‘AR’ ‘BR’ ‘JP’ ‘FR’ ‘NZ’ ‘AU’ ‘ES’
—————————— ———- ———- ———- ———- ———- ———- ———-
A: Below 30,000 20 66 53 102 18 66 92
B: 30,000 – 49,999 12 34 27 221 9 33 99
C: 50,000 – 69,999 24 65 52 283 17 64 158
D: 70,000 – 89,999 23 67 51 410 22 67 198
E: 90,000 – 109,999 35 102 80 573 29 100 308
F: 110,000 – 129,999 61 192 152 639 59 204 368
G: 130,000 – 149,999 22 64 49 446 21 69 196
H: 150,000 – 169,999 22 68 50 396 20 67 207
I: 170,000 – 189,999 24 68 53 240 18 65 173
J: 190,000 – 249,999 10 32 26 147 9 32 79
K: 250,000 – 299,999 0 0 0 182 0 0 62
L: 300,000 and above 0 1 0 112 0 0 46
12 rows selected
Em um relatório com referências cruzadas, deseja-se transferir a coluna country_id para a linha de cabeçalho, como ilustrado no exemplo acima. A coluna(country_id) é transformada em linha, como se fosse rotada 90 graus no sentido anti-horário, e passa a ser o cabeçalho.
Os cabeçalhos de cada coluna são os dados da própria tabela, ou seja, os códigos de pais. Embora esses códigos sejam bastante conhecidos, alguém poderia preferir mostrar os nomes dos Países em lugar dos códigos (“Brasil” em lugar de “BR”).
Nesse caso, é necessário fazer um pequeno ajuste na cláusula FOR da consulta, segundo o exemplo mostrado abaixo:
SQL> select * from (
2 select t.cust_income_level as “Faixa salarial”, t.country_id
3 from sh.customers t
4 )
5 pivot
6 (
7 count(country_id)
8 for country_id in (‘AR’ as “Argentina”,’AU’ “Australia”,’JP’
9 “Japao”,’FR’ “Franca”,’ES’ as “Espanha”)
10 )
11 order by 1;
Faixa salarial Argentina Australia Japao Franca Espanha
—————————— ———- ———- ———- ———- ———-
A: Below 30,000 20 66 53 102 92
B: 30,000 – 49,999 12 33 27 221 99
C: 50,000 – 69,999 24 64 52 283 158
D: 70,000 – 89,999 23 67 51 410 198
E: 90,000 – 109,999 35 100 80 573 308
F: 110,000 – 129,999 61 204 152 639 368
G: 130,000 – 149,999 22 69 49 446 196
H: 150,000 – 169,999 22 67 50 396 207
I: 170,000 – 189,999 24 65 53 240 173
J: 190,000 – 249,999 10 32 26 147 79
K: 250,000 – 299,999 0 0 0 182 62
L: 300,000 and above 0 0 0 112 46
12 rows selected
OBS: A cláusula FOR pode incluir pseudônimos para esses valores, que serão transformados nos cabeçalhos de coluna.
2 – OPERADOR UNPIVOT
O operador UNPIVOT converte os dados baseados em colunas em linhas separadas.
usarei como base a tabela CUSTOMERS, que é a mesma utilizada nos exemplos anteriores(pivot).
Para tornar simples mostrar esta operação com um exemplo: Primeiro, vou criar uma tabela de referências cruzadas com a operação pivot:
SQL> create table exemplo_post as
2 select * from (
3 select t.cust_income_level as “Faixa salarial”, t.country_id
4 from sh.customers t
5 )
6 pivot
7 (
8 count(country_id)
9 for country_id in (‘AR’ as “Argentina”,’AU’ “Australia”,’JP’
10 “Japao”,’FR’ “Franca”,’ES’ as “Espanha”)
11 )
12 order by 1;
Table created
Depois de criada a tabela vou conferir os dados:
SQL> select * from exemplo_post;
Faixa salarial Argentina Australia Japao Franca Espanha
—————————— ———- ———- ———- ———- ———-
A: Below 30,000 20 66 53 102 92
B: 30,000 – 49,999 12 33 27 221 99
C: 50,000 – 69,999 24 64 52 283 158
D: 70,000 – 89,999 23 67 51 410 198
E: 90,000 – 109,999 35 100 80 573 308
F: 110,000 – 129,999 61 204 152 639 368
G: 130,000 – 149,999 22 69 49 446 196
H: 150,000 – 169,999 22 67 50 396 207
I: 170,000 – 189,999 24 65 53 240 173
J: 190,000 – 249,999 10 32 26 147 79
K: 250,000 – 299,999 0 0 0 182 62
L: 300,000 and above 0 0 0 112 46
12 rows selected
Vamos conferir a estrutura da tabela:
SQL> desc exemplo_post
Name Type Nullable Default Comments
————– ———— ——– ——- ——–
Faixa salarial VARCHAR2(30) Y
Argentina NUMBER Y
Australia NUMBER Y
Japao NUMBER Y
Franca NUMBER Y
Espanha NUMBER Y
Na consulta abaixo, os dados da tabela deverão ser separados para que as linhas só mostrem o código de país e o total para cada país.
Isto é possível utilizando o operador unpivot conforme mostrado abaixo:
SQL> select *
2 from exemplo_post
3 unpivot
4 (
5 Total
6 for Pais in (“Argentina”,”Australia”,”Japao”,”Franca”,”Espanha”)
7 )
8 order by “Faixa salarial”, Pais ;
Faixa salarial PAIS TOTAL
—————————— ——— —–
A: Below 30,000 Argentina 20
A: Below 30,000 Australia 66
A: Below 30,000 Espanha 92
A: Below 30,000 Franca 102
A: Below 30,000 Japao 53
B: 30,000 – 49,999 Argentina 12
B: 30,000 – 49,999 Australia 33
B: 30,000 – 49,999 Espanha 99
B: 30,000 – 49,999 Franca 221
B: 30,000 – 49,999 Japao 27
C: 50,000 – 69,999 Argentina 24
C: 50,000 – 69,999 Australia 64
C: 50,000 – 69,999 Espanha 158
C: 50,000 – 69,999 Franca 283
C: 50,000 – 69,999 Japao 52
D: 70,000 – 89,999 Argentina 23
D: 70,000 – 89,999 Australia 67
D: 70,000 – 89,999 Espanha 198
D: 70,000 – 89,999 Franca 410
D: 70,000 – 89,999 Japao 51
Observe-se que cada nome de coluna se transformou em um valor na coluna PAIS.
CONCLUSÃO
O operador Pivot adiciona uma funcionalidade muito importante a linguagem SQL, em lugar de criar um código complicado e pouco intuitivo com muitas funções de decodificação, é possível usar a função pivot para criar um relatório com referências cruzadas a partir de qualquer tabela relacional.
Da mesma forma, é possível transformar qualquer relatório com referências cruzadas para ser armazenado como tabela relacional mediante o uso de operador unpivot.
Referências:
Documentação Oracle Documentação Oracle2 ORACLE-BASE
ORACLE BLOG 