11g新特性 pivot&unpivot 的行列转换

笔记如下：

--求所有BR中，曾经有大于2次状态是Closed, 并且至少做过一次Unpaid的BR集合
select * from(
select customer_trx_id, status from ar_transaction_history_all
)
pivot
(
count(status)
for status in ('CLOSED' as "CLOSED",'INCOMPLETE' as "INCOMPLETE",'UNPAID' as "UNPAID",'CANCELLED' as "CANCELLED",'PENDING_REMITTANCE' as "PENDING_REMITTANCE",'PENDING_ACCEPTANCE' as "PENDING_ACCEPTANCE")
)
where CLOSED >2 and UNPAID > 0


--先建一个表，显示BR在所有状态的统计次数
create table ar_trx_history_all_matrix as
(
select * from(
select customer_trx_id, status from ar_transaction_history_all
)
pivot
(
count(status)
for status in ('CLOSED' as "CLOSED",'INCOMPLETE' as "INCOMPLETE",'UNPAID' as "UNPAID",'CANCELLED' as "CANCELLED",'PENDING_REMITTANCE' as "PENDING_REMITTANCE",'PENDING_ACCEPTANCE' as "PENDING_ACCEPTANCE")
)
)


select * from ar_trx_history_all_matrix;

--以ar_trx_history_all_matrix为基础，求所有BR中， 最大变更次数的状态，和最小变更次数状态相差11次以上的BR的集合 （列转行）

select * from (
select customer_trx_id, max(state_count) max_value, min(state_count) min_value from (
select * from ar_trx_history_all_matrix
unpivot
(
state_count
for state_code in ("CLOSED","INCOMPLETE","UNPAID","CANCELLED","PENDING_REMITTANCE","PENDING_ACCEPTANCE")
)
) where state_count >0 group by customer_trx_id
) where max_value-min_value>11