oracle unpivot 索引_Pivot 和 Unpivot

Pivot 和 Unpivot

使用简单的 SQL 以电子表格类型的交叉表报表显示任何关系表中的信息，并将交叉表中的所有数据存储到关系表中。

Pivot

如您所知，关系表是表格化的，即，它们以列-值对的形式出现。假设一个表名为 CUSTOMERS。

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

...

and so on ...

注意数据是如何以行值的形式显示的：针对每个客户，该记录显示了客户所在的州以及该客户在商店购物的次数。当该客户从商店购买更多物品时，列 times_purchased 会进行更新。

现在，假设您希望统计一个报表，以了解各个州的购买频率，即，各个州有多少客户只购物一次、两次、三次等等。如果使用常规 SQL，您可以执行以下语句：

select state_code, times_purchased, count(1) cnt

from customers

group by state_code, times_purchased;

输出如下：

ST TIMES_PURCHASED CNT

-- --------------- ----------

CT 0 90

CT 1 165

CT 2 179

CT 3 173

CT 4 173

CT 5 152

...

and so on ...

这就是您所要的信息，但是看起来不太方便。使用交叉表报表可能可以更好地显示这些数据，这样，您可以垂直排列数据，水平排列各个州，就像电子表格一样：

Times_purchased

CT NY NJ ...

and so on ...

1 0 1 0 ...

2 23 119 37 ...

3 17 45 1 ...

...

and so on ...

在 Oracle 数据库 11g 推出之前，您需要针对每个值通过 decode 函数进行以上操作，并将每个不同的值编写为一个单独的列。但是，该方法一点也不直观。

庆幸的是，您现在可以使用一种很棒的新特性 PIVOT 通过一种新的操作符以交叉表格式显示任何查询，该操作符相应地称为 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'

--------------- ---------- ---------- ---------- ---------- ---