oracle unpivot 索引_oracle 11g中的pivot和unpivot转换操作

对于pivot和unpivot这个Oracle 11g的新功能一直就想总结一下，正好赶上论坛中有很多人会提问相关的问题，于是利用空闲时间翻译了一篇相关的网络文章。链接如下：

ITPuber：LuiseDalian翻译

时间：2012.11.29

1 Oracle 11g中的pivot和unpivot查询

转换查询包括将行转换成列(pivot)和将列转换成行(unpivot)，从 而产生交叉表格式的结果。 转换是一项通用的技术，尤其对于报表来讲，正是由于该项技术使得多年来针对Oracle的不同版本，可以使用SQL语句产生转换查询的结果集。从 Oracle 11g开始，包含了显式的转换查询支持，即PIVOT和UNPIVOT关键字。这是对SELECT语句的扩展。

1.1 pivot

这里我们从pivot操作开始。大多数的开发者首先要熟悉转换数据：即多行被聚合，然后转换成列，每一列表示聚合数据的不同范围。语法：

除了新的pivot关键字外，可以看到3个新的pivot子句，描述如下：

 pivot_clause：定义被聚合的列(pivot是一种聚合操作)

 pivot_for_clause：定义被分组和转换的列。

 pivot_in_clause：定义pivot_for_clause子句中列的过滤器(例如使用值的范围来限制结果)。pivot_in_clause中每个值的聚合都将被转换成单独的列

1.1.1 一个简单的例子

这个例子是对pivot语法的简单的演示，使用scott.emp表，这里将通过department

和job来汇总salaries，然后转换对每个部门的汇总到它们各自的列上，在转换之前，先看一下基础数据

下面使用11g的语法来转换数据，对于每个job我们将sal的汇总，对每个部门使用单独一列。 可以看到对每个job按部门对sal的汇总已经变成了列，关于这个例子有几点需要注意，语法和结果：

 本例中的pivot_clause求sal列的和，如果需要可以指定多列和可选的别名。

 pivot操作执行隐式的GROUP BY，使用的列是没有出现在pivot_clause中的列(本例为job

和deptno)。大多数的转换查询都会在列的子集上进行操作，所以会使用存储视图、内联视图或子查询。

 这里的pivot_for_clause，表明我们只想转换deptno的聚合。

 这里的prvot_in_cluase，指明deptno值的范围，这里我们硬编码4个值的列表，这也是为什

么会产生4个转换列原因。如果省略别名，Oracle会使用pivot_in_clause中的值来产生转换后列的别名

如上所述，大多数的转换查询都在是列的特定的子集上来执行。像所有的聚合查询一样，额外列的出现会影响分组。这里只是通过在EMP表带有附加列的情况下进行转换查询，来确认这种影响。

在本例中，除了sal列，其余所有列都成了分组集，其中deptno为转换列。在本例中这个转换没有什么意义。

关于转换语法的非常有趣的一点是它在查询中的位置，即位于FROM和WHERE子句之间。 在下面的例子中，我们限制原来的转换查询在选择(通过添加谓词)出来的一些职位上进行。

上面的例子看起来好像是违反语感的，但是把谓词添加到pivot子句之前会产生语法错误。另外在我们的第1个例子中，我们是使用子查询部分(WITH语句)来定义基列的集合。我们可以使用内联视图(下例)或存储视图(后面的练习)来替代子查询。

1.1.2 给转换列指定别名

在前面的例子中，Oracle使用deptno列的值产生转换后列的名称，然而 我们可以给pivot_clause和pivot_in_clause中的一列或多列指定别名。通常Oracle将根据下面约定给转换列命名。 总结：就是pivot_in_clause中的列，如果指定别名就用别名，如果没有指定就用值；而pivot_cluaus中如果指定别名就连接到 pivot_in_cluase转换后的相应列的名称的后面，如果没有指定就不用。

5 / 41

下面将是这些别名选项的示例。我们已经做过没有任何别名的例子。为了简化起见，我们将输入数据集定义为视图。

给转换查询的所有元素指定别名。(Y, Y)，Oracle将对应别名连接起来产生列名。

pivot_in_clause中的列不指定，pivot_clause中的列指定别名。(N, Y)

Oracle连接pivot_in_clause中的值和聚合列的别名产生列名。

只给

pivot_in_clause中的值指定别名。(Y，N)，Oracle仅使用pivot_in_clause中的别名产生列名

总结：在所有的例子中pivot_in_clause用于所有的转换列的命名，不管我们是指定了值还是别名。因此，我们可以选择对哪些值指定别名。

1.1.3 转换多个列

目前为止，我们的例子都限制在一个聚合和一个转换列，然而我们可以定义多个。在下面的例子中将在pivot_clause中定义2个聚合，而对于deptno范围使用与前面的例子相同的值。新的聚合是对求和的薪水的计数。