Comandos SQL avançados

[Rodrigo MesquitaParticipante]

Oracle Pivot

SQL> desc customers
Name Null? Type
—————————————– ——– —————————
CUST_ID NUMBER(10)
CUST_NAME VARCHAR2(20)
STATE_CODE VARCHAR2(2)
TIMES_PURCHASED NUMBER(3)

select cust_id, state_code, times_purchased
from customers
order by cust_id;

CUST_ID STATE_CODE TIMES_PURCHASED
——- ———- —————
1 CT 1
2 NY 10
3 NJ 2
4 NY 4

———————— PIVOT ————————————–
select * from (
select times_purchased, state_code
from customers t
)
pivot
(
count(state_code)
for state_code in (‘NY’,’CT’,’NJ’,’FL’,’MO’)
)
order by times_purchased
/

TIMES_PURCHASED ‘NY’ ‘CT’ ‘NJ’ ‘FL’ ‘MO’
————- ———- ———- ———- ———- ———-
0 16601 90 0 0 0
1 33048 165 0 0 0
2 33151 179 0 0 0
3 32978 173 0 0 0
4 33109 173 0 1 0