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

当选择此表时：

and so on ...

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

现在考虑一种情况，您希望了解每个州的购买频率 - 也就是说，每个州有多少客户只购物一次、两次、三次等。在常规 SQL 中，您可执行以下语句：

select state_code, times_purchased, count(1) cnt from customers group by state_code, times_purchased;

输出如下：

and so on ...

在 Oracle Database 11 g 之前，您可通过对每个值使用某种解码函数来进行上述操作，并将每个不同的值编写为单独的列。但是，这种方法非常不直观。

幸运的是，您现在有了一个强大的新功能，即“透视”，可通过新运算符（相应地命名为 pivot ）以交叉表格式显示任何查询。下面说明了查询的编写方式：

. 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 ... and so on ...

这显示了 pivot 运算符的威力。state_codes 显示在标题行而不是列中。如图所示，这是传统表格格式的外观：

图 1 传统表格图示

在交叉表报表中，您希望将 Times Purchased 列转置到标题行，如图 2 所示。列变成行，就像列逆时针旋转 90 度成为标题行一样。这种形象的旋转动作需要有一个支点，在此例中，支点恰好是 count(state_code) 表达式。

图 2 透视后的图示

该表达式需要使用查询的语法：

第二行“for state_code ...,”将查询限制为仅这些值。这行代码是必需的，因此，您需要事先知道可能的值。在 XML 格式的查询中放宽了此限制，本文稍后将对此进行介绍。

请注意输出中的标题行：

. TIMES_PURCHASED 'NY' 'CT' 'NJ' 'FL' 'MO' --------------- ---------- ---------- ---------- ---------- ----------

列标题是表本身中的数据：州代码。这些缩写可能是不言而喻的，但假设您要显示州名称而不是缩写（例如要显示“Connecticut”而不是“CT”）。在这种情况下，您必须在查询中的 FOR 子句中进行一点调整，如下所示：

count(state_code) for state_code in ('NY' as "New York",'CT' "Connecticut",'NJ' "New Jersey",'FL' "Florida",'MO' as "Missouri") order by 1 Puchase Frequency New York Connecticut New Jersey Florida Missouri ----------------- ---------- ----------- ---------- ---------- ---------- 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 and so on ...

FOR 子句可在语句中为这些值使用别名，这些别名将成为列标题。

有物质，就有反物质；同样对于透视，应该有“逆透视”，对吧？

开个玩笑，不过确实需要透视操作的反向操作。假设您有一个电子表格，其中显示了如下所示的交叉表报表：

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

电子表格数据必须去规范化为关系格式，然后进行存储。当然，您可使用 DECODE 编写复杂的 SQL*:Loader 或 SQL 脚本，将数据加载到 CUSTOMERS 表中。或者，您可使用 pivot 的反向操作“逆透视”将列变为行，这在 Oracle Database 11 g 中是可行的。

举个例子可能更容易证明这一点。首先，使用 pivot 操作创建一个交叉表：

3 select * from ( 4 select times_purchased as "Puchase Frequency", state_code 5 from customers t 7 pivot 9 count(state_code) 10 for state_code in ('NY' as "New York",'CT' "Conn",'NJ' "New Jersey",'FL' "Florida",'MO' as "Missouri") 12* order by 1

您可检查数据在表中的存储方式：

Puchase Frequency New York Conn New Jersey Florida Missouri ----------------- ---------- ---------- ---------- ---------- --------- 1 33048 165 0 0 0 2 33151 179 0 0 0 3 32978 173 0 0 0 4 33109 173 0 1 0 ... and so on ...

下面说明数据在电子表格中的存储方式：每个州都是表中的一列（“New York”、“Conn”，依此类推）。

SQL> desc cust_matrix Name Null? Type ----------------------------------------- -------- --------------------------- Puchase Frequency NUMBER(3) New York NUMBER Conn NUMBER New Jersey NUMBER Florida NUMBER Missouri NUMBER

您需要分解表，使行只显示州代码和该州的相应计数。这可通过 unpivot 操作来完成，如下所示：

for state_code in ("New York","Conn","New Jersey","Florida","Missouri") order by "Puchase Frequency", state_code

输出如下：

Puchase Frequency STATE_CODE STATE_COUNTS ----------------- ---------- ------------ 1 Conn 165 1 Florida 0 1 Missouri 0 1 New Jersey 0 1 New York 33048 2 Conn 179 2 Florida 0 2 Missouri 0 and so on ...

请注意每个列名称如何成为 STATE_CODE 列中的值。Oracle 如何知道 state_code 是列名？它从查询中的以下子句知道这一点：

for state_code in ("New York","Conn","New Jersey","Florida","Missouri")

在此处，您指定值“New York”、“Conn”等是要逆透视的新列（称为 state_code）的值。看一下下面的部分原始数据：

Purchase Frequency New York Conn New Jersey Florida Missouri ----------------- ---------- ---------- ---------- ---------- - --------- 1 33048 165 0 0 0

当列“New York”突然变成行中的值时，该如何显示值 33048，放在哪一列下方？该问题的答案就是上述查询中 unpivot 运算符内紧邻 for 子句上方的子句。指定 state_counts 时，它就是在结果输出中创建的新列的名称。

Unpivot 可能是 pivot 的相反操作，但不应假定前者可逆转后者的操作。例如，在上例中，对表 CUSTOMERS 使用 pivot 操作创建了一个新表 CUST_MATRIX。后来，对表 CUST_MATRIX 使用了 unpivot ，但该操作没有取回原始表 CUSTOMERS 的详细信息。相反，交叉表报表以不同的方式显示，可供加载到关系表中。因此， unpivot 并不是要撤消 pivot 所执行的操作，在创建数据透视表然后删除原始表之前，应认真考虑这一事实。

unpivot 的一些非常有趣的用法超出了通常功能强大的数据操纵，例如前面显示的示例。Amis Technologies 的 Oracle ACE 总监 Lucas Jellema 演示了如何 生成多行特定数据用于测试 。在此，我将略微修改他的原始代码来生成英语字母的元音：

for state_code in ('NY','CT','NJ','FL','MO')

此要求假定您知道 state_code 列中存在哪些值。如果不知道哪些值可用，将如何构造查询？

pivot 操作中有另一个子句 XML，它可将透视输出创建为 XML，在其中您可指定特殊子句 ANY 而非文字值。以下是示例：

select * from ( select times_purchased as "Purchase Frequency", state_code from customers t pivot xml count(state_code) for state_code in (any) order by 1

输出以 CLOB 的形式返回，因此在运行查询之前，请确保将 LONGSIZE 设置为较大的值。

SQL> set long 99999

与原始的 pivot 操作相比，此查询有两个明显的不同点（以粗体显示）。首先，您指定了一个子句 pivot xml，而不只是 pivot 。它以 XML 形式创建输出。其次，for 子句显示 for state_code in (any)，而不是一长串 state_code 值。利用 XML 表示法，您可使用 ANY 关键字，而无需输入 state_code 值。输出如下：

Purchase Frequency STATE_CODE_XML ------------------ -------------------------------------------------- 1 CT 165 NY 33048 2 CT 179 NY 33151 ... and so on ...

如您所见，STATE_CODE_XML 列为 XMLTYPE，其根元素为 。每个值都表示为名称/值元素对。您可使用任何 XML 解析器中的输出生成更有用的输出。

除了 ANY 子句之外，您还可编写一个子查询。假设您有一个首选州列表，并且只想选择这些州所对应的行。将首选州放置在名为 preferred_states 的新表中：

for state_code in (select distinct state_code from customers)

子查询必须返回不同的值；否则，查询将会失败。这就是为什么我们在上文指定了 DISTINCT 子句。

Pivot 在 SQL 语言中添加了非常重要的实用功能。您可使用透视函数在任何关系表上创建交叉表报表，而不用编写包含许多解码函数、复杂且不直观的代码。同样，您可使用 unpivot 操作转换任何交叉表报表，并将其存储为常规关系表。 Pivot 可生成采用常规文本或 XML 形式的输出。在后一种情况下，您无需指定透视操作需要搜索的值的域。

有关透视和逆透视操作的更多信息，请参考 Oracle Database 11g SQL 语言参考 。

返回系列目录