Oracle PIVOT和UNPIVOT

末蓝、 2023-10-05 06:59 50阅读 0赞

您是否曾经需要将一组数据从行转换为列？您可以使用Oracle PIVOT功能（相反，使用Oracle UNPIVOT）进行此操作。

**目录**

本指南方涵盖以下主题。您可以单击以下任一条目进入本页的该部分：

*  问题
 *  Oracle SQL中的PIVOT关键字
 *  简单的PIVOT示例
 *  指定分组的列
 *  在PIVOT中使用WHERE子句
 *  别名PIVOT列
 *  执行多个聚合
 *  按多列分组
 *  XML的PIVOT示例
 *  动态指定列
 *  使用UNPIVOT将列转换为行
 *  一个简单的UNPIVOT示例
 *  在UNPIVOT中处理NULL值
 *  别名和UNPIVOT
 *  您可以不使用Oracle PIVOT进行汇总吗？

现在，让我们进入指南！

**问题**

假设您在名为cust\_sales的表中拥有这组数据：

<table> 
 <tbody> 
 <tr> 
 <td> 位置 </td> 
 <td> 顾客ID </td> 
 <td> sale_amount </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 6 </td> 
 <td> 875 </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> 2 </td> 
 <td> 378 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 5 </td> 
 <td> 136 </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 5 </td> 
 <td> 236 </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> 3 </td> 
 <td> 174 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 1 </td> 
 <td> 729 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 2 </td> 
 <td> 147 </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 3 </td> 
 <td> 200 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 6 </td> 
 <td> 987 </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> 4 </td> 
 <td> 584 </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> 3 </td> 
 <td> 714 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 1 </td> 
 <td> 192 </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 3 </td> 
 <td> 946 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 4 </td> 
 <td> 649 </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> 2 </td> 
 <td> 503 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 5 </td> 
 <td> 399 </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> 6 </td> 
 <td> 259 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 3 </td> 
 <td> 407 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#eeeeee;"> 西方 </td> 
 <td> 1 </td> 
 <td style="border-color:#eeeeee;"> 545 </td> 
 </tr> 
 </tbody> 
</table>

这代表了不同地区不同客户的一组销售。如果要查找每个位置和每个客户的销售[总和][Link 1]怎么办？您可以使用SUM和[GROUP BY][]编写SQL查询：

SELECT location, customer\_id, SUM(sale\_amount)

FROM cust\_sales

GROUP BY location, customer\_id

ORDER BY location, customer\_id;

这将显示以下结果：

<table> 
 <tbody> 
 <tr> 
 <td> 位置 </td> 
 <td> 顾客ID </td> 
 <td> SUM（SALE_AMOUNT） </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> 3 </td> 
 <td> 174 </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> 4 </td> 
 <td> 584 </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> 6 </td> 
 <td> 259 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 1 </td> 
 <td> 192 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 2 </td> 
 <td> 147 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 3 </td> 
 <td> 407 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 4 </td> 
 <td> 649 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 5 </td> 
 <td> 136 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 1 </td> 
 <td> 729 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 5 </td> 
 <td> 399 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 6 </td> 
 <td> 1862 </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> 2 </td> 
 <td> 881 </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> 3 </td> 
 <td> 714 </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 1 </td> 
 <td> 545 </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 3 </td> 
 <td> 1146 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#eeeeee;"> 西方 </td> 
 <td> 5 </td> 
 <td style="border-color:#eeeeee;"> 236 </td> 
 </tr> 
 </tbody> 
</table>

如果您不想在位置和customer\_id中显示两列，而是在每一行中显示位置，并在每一列中显示customer\_id，该怎么办？您将寻找这样的结果：

这通常称为枢转，或转置行和列，或转置列和行。可以用SQL中的几种方法完成，最简单的方法是使用Oracle PIVOT关键字。

窗体底端

**Oracle SQL****中的****PIVOT****关键字**

Oracle可以创建结果集，该结果集可以转置或旋转列和行以提供摘要。这是使用SQL PIVOT关键字完成的。此关键字是在Oracle 11g中引入的。

此关键字应用于[SELECT语句][SELECT]，如下所示：

SELECT columns

FROM tables

PIVOT \[XML\] (

pivot\_clause,

pivot\_for\_clause,

pivot\_in\_clause

);

在PIVOT关键字之后，它包含几个组件：

*  **XML**：这是一个可选关键字，它使您可以XML格式输出数据。下面有一个例子。
 *  **pivot\_clause**：这定义查询将在其上聚合数据的内容，因为PIVOT关键字聚合数据。
 *  **pivot\_for\_clause**：这定义了将对哪些列进行分组和**透视**。
 *  **pivot\_in\_clause**：用于过滤pivot\_for\_clause中各列的值。此子句中的每个值将是一个单独的列。

如果这让您感到困惑，请放心。下面的示例将使其更容易理解。

**简单的****PIVOT****示例**

假设您想显示前面提到的结果：第一列中的位置，每个客户都有不同的列，并且sale\_amount的SUM作为值。

在没有透视的标准GROUP BY中显示此数据的查询是：

SELECT location,

customer\_id,

SUM(sale\_amount)

FROM cust\_sales

GROUP BY location, customer\_id

ORDER BY location, customer\_id;

<table> 
 <tbody> 
 <tr> 
 <td> 位置 </td> 
 <td> 顾客ID </td> 
 <td> SUM（SALE_AMOUNT） </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> 3 </td> 
 <td> 174 </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> 4 </td> 
 <td> 584 </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> 6 </td> 
 <td> 259 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 1 </td> 
 <td> 192 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 2 </td> 
 <td> 147 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 3 </td> 
 <td> 407 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 4 </td> 
 <td> 649 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 5 </td> 
 <td> 136 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 1 </td> 
 <td> 729 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 5 </td> 
 <td> 399 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 6 </td> 
 <td> 1862 </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> 2 </td> 
 <td> 881 </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> 3 </td> 
 <td> 714 </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 1 </td> 
 <td> 545 </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 3 </td> 
 <td> 1146 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#eeeeee;"> 西方 </td> 
 <td> 5 </td> 
 <td style="border-color:#eeeeee;"> 236 </td> 
 </tr> 
 </tbody> 
</table>

将其转换为透视结果集的查询如下所示：

SELECT \*

FROM cust\_sales

PIVOT (

SUM(sale\_amount)

FOR customer\_id

IN (1, 2, 3, 4, 5, 6)

);

让我们分解一下这个查询：

*  **选择****\***。我们从SELECT \* FROM cust\_sales开始。通常我不喜欢使用SELECT \*，但是如果我们单独列出各列，则将显示错误，因为应该由PIVOT子句确定显示的内容。
 *  **PIVOT**：这表明我们要使用SQL数据透视功能来显示输出。我们打开括号以包含一系列参数。
 *  **SUM****（****sale\_amount****）**：这是要在输出中间显示的值。这是一个应用了聚合函数的数值。
 *  **FOR customer\_id**：这是表中的列，用于在输出中显示不同的列。这些值显示为列标题。
 *  **IN****（****1****、****2****、****3****、****4****、****5****、****6****）**：这些是上述用于输出的FOR列的值。就像WHERE过滤器一样。您不必指定所有列，但是我在这里。

FOR子句中未提及的任何列均显示为行并进行分组。在这种情况下，将使用位置字段。

该查询的输出为：

这是PIVOT关键字的结果。行是不同的位置，列是customer\_id 1到6，值是sale\_amount的总和。任何NULL值都是因为不存在数据。例如，西部位置没有customer\_id 2的销售。

**指定分组的列**

使用PIVOT关键字时，FOR子句中未提及的任何列都将用作Oracle PIVOT GROUP BY的一部分。在上面的示例中，唯一的一列是location列，这是可以的。

但是，如果您的输出包含另一个称为prod\_category的列怎么办？

<table> 
 <tbody> 
 <tr> 
 <td> 位置 </td> 
 <td> prod_category </td> 
 <td> 顾客ID </td> 
 <td> sale_amount </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 家具类 </td> 
 <td> 2 </td> 
 <td> 875 </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> 电子产品 </td> 
 <td> 2 </td> 
 <td> 378 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 园艺 </td> 
 <td> 4 </td> 
 <td> 136 </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 电子产品 </td> 
 <td> 3 </td> 
 <td> 236 </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> 家具类 </td> 
 <td> 3 </td> 
 <td> 174 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 电子产品 </td> 
 <td> 1 </td> 
 <td> 729 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 园艺 </td> 
 <td> 2 </td> 
 <td> 147 </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 电子产品 </td> 
 <td> 3 </td> 
 <td> 200 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 家具类 </td> 
 <td> 4 </td> 
 <td> 987 </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> 园艺 </td> 
 <td> 4 </td> 
 <td> 584 </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> 电子产品 </td> 
 <td> 3 </td> 
 <td> 714 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 家具类 </td> 
 <td> 1 </td> 
 <td> 192 </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 园艺 </td> 
 <td> 3 </td> 
 <td> 946 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 电子产品 </td> 
 <td> 4 </td> 
 <td> 649 </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> 家具类 </td> 
 <td> 2 </td> 
 <td> 503 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 电子产品 </td> 
 <td> 1 </td> 
 <td> 399 </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> 园艺 </td> 
 <td> 3 </td> 
 <td> 259 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 电子产品 </td> 
 <td> 3 </td> 
 <td> 407 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#eeeeee;"> 西方 </td> 
 <td> 家具类 </td> 
 <td> 1 </td> 
 <td style="border-color:#eeeeee;"> 545 </td> 
 </tr> 
 </tbody> 
</table>

如果您运行相同的PIVOT查询，则会得到此结果。在此示例中，我使用了另一个表cust\_sales\_category并减少了客户数量。

SELECT \*

FROM cust\_sales\_category

PIVOT (

SUM(sale\_amount)

FOR customer\_id

IN (1, 2, 3, 4)

);

结果是：

<table> 
 <tbody> 
 <tr> 
 <td> 位置 </td> 
 <td> prod_category </td> 
 <td> 1 </td> 
 <td> 2 </td> 
 <td> 3 </td> 
 <td> 4 </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 园艺 </td> 
 <td> （空值） </td> 
 <td> （空值） </td> 
 <td> 946 </td> 
 <td> （空值） </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 家具类 </td> 
 <td> 545 </td> 
 <td> （空值） </td> 
 <td> （空值） </td> 
 <td> （空值） </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 电子产品 </td> 
 <td> （空值） </td> 
 <td> （空值） </td> 
 <td> 407 </td> 
 <td> 649 </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> 家具类 </td> 
 <td> （空值） </td> 
 <td> （空值） </td> 
 <td> 174 </td> 
 <td> （空值） </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 家具类 </td> 
 <td> （空值） </td> 
 <td> 875 </td> 
 <td> （空值） </td> 
 <td> 987 </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 家具类 </td> 
 <td> 192 </td> 
 <td> （空值） </td> 
 <td> （空值） </td> 
 <td> （空值） </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> 家具类 </td> 
 <td> （空值） </td> 
 <td> 503 </td> 
 <td> （空值） </td> 
 <td> （空值） </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> 园艺 </td> 
 <td> （空值） </td> 
 <td> 147 </td> 
 <td> （空值） </td> 
 <td> 136 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 电子产品 </td> 
 <td> 1128 </td> 
 <td> （空值） </td> 
 <td> （空值） </td> 
 <td> （空值） </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 电子产品 </td> 
 <td> （空值） </td> 
 <td> （空值） </td> 
 <td> 436 </td> 
 <td> （空值） </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> 园艺 </td> 
 <td> （空值） </td> 
 <td> （空值） </td> 
 <td> 259 </td> 
 <td> 584 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#eeeeee;"> 南方 </td> 
 <td> 电子产品 </td> 
 <td> （空值） </td> 
 <td> 378 </td> 
 <td> 714 </td> 
 <td style="border-color:#eeeeee;"> （空值） </td> 
 </tr> 
 </tbody> 
</table>

我们的数据按位置和prod\_category分组。发生这种情况是因为在FOR子句中提到了customer\_id，并且该语句使用了GROUP BY的所有其他列。

如果我们不想按位置和prod\_category分组怎么办？

我们可以尝试将SELECT查询更改为仅选择location列。

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

PIVOT (

SUM(sale\_amount)

FOR customer\_id

IN (1, 2, 3, 4)

);

该查询的结果是：

ORA-00904：“ SALE\_AMOUNT”：无效的标识符

00904. 00000-“％s：无效的标识符”

\*原因：

\*行动：

行错误：87列：31

由于无法将各个列指定为SELECT子句的一部分，因此出现此错误。但是，有两种方法可以执行此操作：WITH子句或子查询。

要指定作为PIVOT查询的一部分进行分组的列，可以将Oracle PIVOT与子查询一起使用，其中子查询仅显示所需的列：

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT (

SUM(sale\_amount)

FOR customer\_id

IN (1, 2, 3, 4)

);

注意，如何从子查询中执行SELECT \*，而不是直接从表中进行选择，子查询提到了各个列。这意味着prod\_category被忽略，并且您得到如下结果：

结果根本没有将prod\_category列分组。如果要按prod\_category而不是位置进行分组，只需更改子查询中的字段即可：

SELECT \*

FROM (

SELECT prod\_category, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT (

SUM(sale\_amount)

FOR customer\_id

IN (1, 2, 3, 4)

);

这样便可以通过使用PIVOT查询来更改要显示和分组的列：通过将Oracle PIVOT与子查询一起使用。

**在****PIVOT****中使用****WHERE****子句**

在上面的查询中，结果显示了所有数据的关键摘要。所有记录均按几个字段分组，并显示销售金额的总和。

如果您想将其限制为仅某些行怎么办？

您可以使用WHERE子句，就像普通的SELECT查询一样。

但是，如果将WHERE子句放在中间，则会出现错误：

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

WHERE location <> 'south'

PIVOT (

SUM(sale\_amount)

FOR customer\_id

IN (1, 2, 3, 4)

);

ORA-00933：SQL命令未正确结束

00933.00000-“ SQL命令未正确结束”

\*原因：

\*行动：

行错误：127列：1

这是因为PIVOT子句必须位于WHERE子句之后。正确的查询如下所示：

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT (

SUM(sale\_amount)

FOR customer\_id

IN (1, 2, 3, 4)

)

WHERE location <> 'south';

WHERE子句在查询的末尾，在SQL PIVOT子句之后。显示以下结果：

结果不包括位置等于“南方”的记录。

如果使用子查询方法确定列，则还可以将WHERE子句放在子查询中：

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

WHERE location <> 'south'

)

PIVOT (

SUM(sale\_amount)

FOR customer\_id

IN (1, 2, 3, 4)

);

您将获得相同的结果。

**别名****PIVOT****列**

到目前为止，我们查看的查询将列标题显示为存储在表中的customer\_id值。如果您想给他们一个不同的名字怎么办？PIVOT关键字允许您指定列别名。这可以在pivot\_clause和pivot\_in\_clause上完成。

此示例为SUM值提供别名：

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT (

SUM(sale\_amount) AS sales\_total

FOR customer\_id

IN (1, 2, 3, 4)

);

您可以看到我们已经使用AS sales\_total为SUM（sale\_amount）别名。该查询的结果是：

<table> 
 <tbody> 
 <tr> 
 <td> 位置 </td> 
 <td> 1_SALES_TOTAL </td> 
 <td> 2_SALES_TOTAL </td> 
 <td> 3_SALES_TOTAL </td> 
 <td> 4_SALES_TOTAL </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 545 </td> 
 <td> （空值） </td> 
 <td> 1382 </td> 
 <td> （空值） </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> （空值） </td> 
 <td> （空值） </td> 
 <td> 433 </td> 
 <td> 584 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 1128 </td> 
 <td> 875 </td> 
 <td> （空值） </td> 
 <td> 987 </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> （空值） </td> 
 <td> 881 </td> 
 <td> 714 </td> 
 <td> （空值） </td> 
 </tr> 
 <tr> 
 <td style="border-color:#eeeeee;"> 东方 </td> 
 <td> 192 </td> 
 <td> 147 </td> 
 <td> 407 </td> 
 <td style="border-color:#eeeeee;"> 785 </td> 
 </tr> 
 </tbody> 
</table>

Oracle PIVOT列名称现在显示为1\_SALES\_TOTAL，2\_SALES\_TOTAL，依此类推。这由<customer\_id> \_ <alias\_name>确定。1\_SALES\_TOTAL的第一列是customer\_id 1的sales\_total值。

相反，您可以为IN子句中的列值加上别名：

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT (

SUM(sale\_amount)

FOR customer\_id

IN (1 AS cust1, 2 AS cust2, 3 AS cust3, 4 AS cust4)

);

列值正好显示您对它们的别名。它没有显示1，而是显示CUST1。

最后，您可以结合使用pivot\_clause别名和pivot\_in\_clause别名：

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT (

SUM(sale\_amount) AS sales\_total

FOR customer\_id

IN (1 AS cust1, 2 AS cust2, 3 AS cust3, 4 AS cust4)

);

<table> 
 <tbody> 
 <tr> 
 <td> 位置 </td> 
 <td> CUST1_SALES_TOTAL </td> 
 <td> CUST2_SALES_TOTAL </td> 
 <td> CUST3_SALES_TOTAL </td> 
 <td> CUST4_SALES_TOTAL </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 545 </td> 
 <td> （空值） </td> 
 <td> 1382 </td> 
 <td> （空值） </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> （空值） </td> 
 <td> （空值） </td> 
 <td> 433 </td> 
 <td> 584 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 1128 </td> 
 <td> 875 </td> 
 <td> （空值） </td> 
 <td> 987 </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> （空值） </td> 
 <td> 881 </td> 
 <td> 714 </td> 
 <td> （空值） </td> 
 </tr> 
 <tr> 
 <td style="border-color:#eeeeee;"> 东方 </td> 
 <td> 192 </td> 
 <td> 147 </td> 
 <td> 407 </td> 
 <td style="border-color:#eeeeee;"> 785 </td> 
 </tr> 
 </tbody> 
</table>

这已[级联][Link 2]无论从pivot\_in\_clause（cust1）别名并从pivot\_clause（sales\_total）别名，通过下划线将它们分离：cust1\_sales\_total。

**执行多个聚合**

在到目前为止的示例中，我们在单个列上执行了一次聚合：对不同的customer\_id组合进行了SUM运算。如果需要，我们可以在SQL PIVOT查询中扩展它，以执行更多操作。

我们可以在我们的PIVOT查询中添加第二个[聚合函数][Link 3]。例如，假设要显示SUM，我们还要显示每个组中的COUNT个记录。查询如下所示：

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT (

SUM(sale\_amount),

COUNT(sale\_amount)

FOR customer\_id

IN (1, 2, 3, 4)

);

如果运行此查询，则会出现错误：

ORA-00918：列定义不明确

00918. 00000-“列定义不明确”

\*原因：

\*行动：

为了解决这个问题，我们需要给SUM和COUNT子句一个别名：

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT (

SUM(sale\_amount) AS sum\_sales,

COUNT(sale\_amount) AS count\_sales

FOR customer\_id

IN (1, 2, 3, 4)

);

该查询的结果是：

<table> 
 <tbody> 
 <tr> 
 <td> 位置 </td> 
 <td> 1_SUM_SALES </td> 
 <td> 1_COUNT_SALES </td> 
 <td> 2_SUM_SALES </td> 
 <td> 2_COUNT_SALES </td> 
 <td> 3_SUM_SALES </td> 
 <td> 3_COUNT_SALES </td> 
 <td> 4_SUM_SALES </td> 
 <td> 4_COUNT_SALES </td> 
 </tr> 
 <tr> 
 <td> 西方 </td> 
 <td> 545 </td> 
 <td> 1 </td> 
 <td> （空值） </td> 
 <td> 0 </td> 
 <td> 1382 </td> 
 <td> 3 </td> 
 <td> （空值） </td> 
 <td> 0 </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> （空值） </td> 
 <td> 0 </td> 
 <td> （空值） </td> 
 <td> 0 </td> 
 <td> 433 </td> 
 <td> 2 </td> 
 <td> 584 </td> 
 <td> 1 </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> 1128 </td> 
 <td> 2 </td> 
 <td> 875 </td> 
 <td> 1 </td> 
 <td> （空值） </td> 
 <td> 0 </td> 
 <td> 987 </td> 
 <td> 1 </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> （空值） </td> 
 <td> 0 </td> 
 <td> 881 </td> 
 <td> 2 </td> 
 <td> 714 </td> 
 <td> 1 </td> 
 <td> （空值） </td> 
 <td> 0 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#eeeeee;"> 东方 </td> 
 <td> 192 </td> 
 <td> 1 </td> 
 <td> 147 </td> 
 <td> 1 </td> 
 <td> 407 </td> 
 <td> 1 </td> 
 <td> 785 </td> 
 <td style="border-color:#eeeeee;"> 2 </td> 
 </tr> 
 </tbody> 
</table>

您可以看到，对于customer\_id的每个值，将显示销售的SUM，然后显示COUNT。列别名已包括customer\_id和sum\_sales或count\_sales的别名。这使我们可以在多个列上使用Oracle PIVOT。

**按多列分组**

在PIVOT查询中使用多列的另一种方法是按多列分组。到目前为止，我们仅按customer\_id分组。如果要按customer\_id和类别分组怎么办？

你可以那样做

SELECT \*

FROM (

SELECT location, prod\_category, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT (

SUM(sale\_amount)

FOR (customer\_id, prod\_category)

IN (

(1, 'furniture') AS furn1,

(2, 'furniture') AS furn2,

(1, 'electronics') AS elec1,

(2, 'electronics') AS elec2

)

);

在此查询中，FOR子句包括两列：customer\_id和prod\_category。此prod\_category也已在开始时添加到SELECT子查询中，因为之前未包含它。

现在，我们在FOR子句中指定了两列，我们需要将这两列都添加到IN子句中。现在，每个IN标准都包含在方括号中，该方括号指定了customer\_id和prod\_category的值（例如1，“ furniture”）。仅在此示例中，我们排除了许多其他记录。

我们还为他们提供了列别名，以使其更易于阅读。

如果运行此查询，将得到以下结果：

结果显示了家具和电子产品类别以及customer\_id 1和2的销售总和。可以根据需要根据需要在IN子句中定制此处的组。

**XML****的****PIVOT****示例**

PIVOT关键字允许您以XML格式显示结果。这就像在PIVOT关键字之后添加XML关键字一样简单。使用前面的示例，我们可以以XML格式显示输出。

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT XML (

SUM(sale\_amount) AS sales\_total

FOR customer\_id

IN (1, 2, 3, 4)

);

如果仅添加XML关键字并运行此查询，则会出现以下错误：

ORA-00905：缺少关键字

00905. 00000-“缺少关键字”

\*原因：

\*行动：

这是因为我们无法在IN子句中指定值。我们将需要使用子查询或使用关键字ANY。

**具有****ANY****的****XML**

这是带有XML参数和ANY关键字的查询的示例。

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT XML (

SUM(sale\_amount) AS sales\_total

FOR customer\_id

IN (ANY)

);

使用关键字ANY表示对customer\_id的所有值进行分组。该查询的结果是：

<table> 
 <tbody> 
 <tr> 
 <td> 位置 </td> 
 <td> CUSTOMER_ID_XML </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> （XMLTYPE） </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> （XMLTYPE） </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> （XMLTYPE） </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> （XMLTYPE） </td> 
 </tr> 
 <tr> 
 <td style="border-color:#eeeeee;"> 西方 </td> 
 <td style="border-color:#eeeeee;"> （XMLTYPE） </td> 
 </tr> 
 </tbody> 
</table>

默认情况下，SQL Developer将为这些XML列显示“ XMLTYPE”的值。如果您将其作为脚本（而不是网格）运行，或者作为应用程序的一部分运行，它将返回完整值。如果要在网格中看到它，可以在SQL Developer中更改设置：

1.  打开工具菜单，然后单击首选项。
2.  展开数据库部分，然后单击高级。
3.  选中“在网格中显示XML值”框
4.  点击确定

现在，您可以重新运行查询，并显示完整值。

<table> 
 <tbody> 
 <tr> 
 <td> 位置 </td> 
 <td> CUSTOMER_ID_XML </td> 
 </tr> 
 <tr> 
 <td> 中央 </td> 
 <td> &lt;PivotSet&gt; &lt;item&gt; &lt;列名称=“ CUSTOMER_ID”&gt; 3 &lt;/ column&gt;… </td> 
 </tr> 
 <tr> 
 <td> 东方 </td> 
 <td> &lt;PivotSet&gt; &lt;item&gt; &lt;列名称=“ CUSTOMER_ID”&gt; 1 &lt;/ column&gt;… </td> 
 </tr> 
 <tr> 
 <td> 北方 </td> 
 <td> &lt;PivotSet&gt; &lt;item&gt; &lt;列名=“ CUSTOMER_ID”&gt; 1 &lt;/ column&gt; </td> 
 </tr> 
 <tr> 
 <td> 南方 </td> 
 <td> &lt;PivotSet&gt; &lt;item&gt; &lt;列名称=“ CUSTOMER_ID”&gt; 2 &lt;/ column&gt; </td> 
 </tr> 
 <tr> 
 <td style="border-color:#eeeeee;"> 西方 </td> 
 <td style="border-color:#eeeeee;"> &lt;PivotSet&gt; &lt;item&gt; &lt;列名=“ CUSTOMER_ID”&gt; 1 &lt;/ column&gt; </td> 
 </tr> 
 </tbody> 
</table>

数据被转换为XML格式。上面的值已缩短以清理表，完整值如下所示：

如果您的应用程序正在处理XML，这将很有用。但是，与普通网格相比，它有点难以阅读。

**带有子查询的****XML**

在使用XML输出的数据透视查询中定义列的另一种方法是使用子查询。我们之前使用关键字ANY的查询在IN子句中定义列。

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT XML (

SUM(sale\_amount) AS sales\_total

FOR customer\_id

IN (ANY)

);

可以使用子查询来定义要包括的列值，而不是使用ANY关键字。

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT XML (

SUM(sale\_amount) AS sales\_total

FOR customer\_id

IN (

SELECT customer\_id

FROM cust\_sales\_category

)

);

IN子句中的子查询为：

SELECT customer\_id FROM cust\_sales\_category

这将从该表中找到所有的customer\_id值。其结果与ANY关键字相同。

使用子查询意味着您可以更好地控制使用哪些组。您可以在此子查询中添加WHERE子句以仅显示某些customer\_id值：

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT XML (

SUM(sale\_amount) AS sales\_total

FOR customer\_id

IN (

SELECT customer\_id

FROM cust\_sales\_category

WHERE customer\_id <= 3

)

);

这将为所有小于或等于3的customer\_id创建列组。

我根本没有使用过这个XML输出，但是如果您需要在查询或应用程序中使用它，那么它就非常有用。

**动态指定列**

在普通的数据透视查询中，或者在将数据输出为列而不是XML的查询中，必须指定要包含在组中的列：

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT (

SUM(sale\_amount)

FOR customer\_id

IN (1, 2, 3, 4)

);

如果要按所有这些值分组，是否有办法这么说，而不是单独列出所有值？

不幸的是，没有。除非您已应用XML关键字，否则您不能在IN子句中使用ANY关键字或使用子查询。

此查询将返回错误：

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT (

SUM(sale\_amount)

FOR customer\_id

IN (ANY)

);

ORA-00936：缺少表达

00936. 00000-“缺少表达”

\*原因：

\*行动：

**使用****UNPIVOT****将列转换为行**

我们上面看过的SQL PIVOT关键字会将行转换为列。Oracle提供了UNPIVOT关键字，其作用相反。它将列转换为行。

SQL UNPIVOT关键字的语法为：

SELECT columns

FROM table

UNPIVOT \[INCLUDE|EXCLUDE NULLS\] (

unpivot\_clause,

unpivot\_for\_clause,

unpivot\_in\_clause

)

WHERE criteria;

它具有与PIVOT关键字类似的结构，但有一些区别：

*  它允许您以特定方式处理NULL值。
 *  它不包含XML关键字。
 *  它不会取消聚合行，因为查询不了解聚合后的数据。

有一些带有UNPIVOT关键字的子句：

*  unpivot\_clause：这为数据透视表中的每个列值指定列的名称。
 *  unpivot\_for\_clause：这指定数据透视表中显示的数字值的列名称。
 *  unpivot\_in\_clause：这指定了透视列的列表。

如果这听起来令人困惑，那么一些示例将有助于更好地解释它。

**一个简单的****UNPIVOT****示例**

为了演示SQL UNPIVOT关键字，我们需要首先具有一些数据透视。我们可以提供一个子查询，但是UNPIVOT查询将非常混乱。因此，让我们[创建一个][Link 4]显示数据[透视图的视图][Link 4]。

我们可以创建一个名为pivod\_sales的视图。

CREATE VIEW pivoted\_sales AS

SELECT \*

FROM (

SELECT location, customer\_id, sale\_amount

FROM cust\_sales\_category

)

PIVOT (

SUM(sale\_amount)

FOR customer\_id

IN (

1 AS cust1,

2 AS cust2,

3 AS cust3,

4 AS cust4

)

);

创建视图后，我们可以从中选择以检查数据：

SELECT \*

FROM pivoted\_sales;

现在，让我们来看一个UNPIVOT关键字的示例。

SELECT \*

FROM pivoted\_sales

UNPIVOT (

total\_sales

FOR customer\_id

IN (cust1, cust2, cust3, cust4)

);

该查询的结果是：

在此查询中：

*  unpivot\_clause是“ total\_sales”，其结果是最后一列的名称。此列包含每个客户和地区的数字值或销售总额。
 *  unpivot\_for\_clause是“ customer\_id”。这是第二列的名称，该列由数据透视表中不同的列标题填充。
 *  unpivot\_in\_clause是来自透视数据的每个列标题。基础表中的列是cust1，cust2，cust3和cust4，这些列在IN子句中指定。

**在****UNPIVOT****中处理****NULL****值**

在上面的输出中，您会注意到返回了13行。但是，有5个地点和4个客户。该查询是否应该返回20个结果，即5 x 4，并因此得出位置和客户的所有组合？为什么查询不这样做？

这是因为我们从中选择的数据对于位置和客户的某些组合没有值。“ west”的位置已返回客户1和3的行，因此这意味着客户2和4没有行。让我们查询表并进行检查。

SELECT \*

FROM pivoted\_sales;

您可以看到位置为“ west”的行中，有一个值cust1和cust3，但没有cust2或cust4。

Oracle UNPIVOT关键字不显示基础数据为NULL的结果。该表中有7个NULL值，因此在20个可能的值中，这7个未显示，因此只有13个值。

此行为是默认行为，但可以更改。如果在UNPIVOT关键字之后添加关键字INCLUDE NULLS，则结果中的记录将为NULL值。

SELECT \*

FROM pivoted\_sales

UNPIVOT INCLUDE NULLS (

total\_sales

FOR customer\_id

IN (cust1, cust2, cust3, cust4)

);

结果显示20行。包括原始的13行以及total\_sales的NULL值的7行。此处使用的INCLUDE NULLS关键字已导致包含这些值。

此处可以使用的另一个关键字是EXCLUDE NULLS。这与默认行为相同，类似于许多其他参数，例如默认为ORDER BY ASC。如果您想要某些东方西专门排除NULL值而不依赖默认值，那么最好指定它：

SELECT \*

FROM pivoted\_sales

UNPIVOT EXCLUDE NULLS (

total\_sales

FOR customer\_id

IN (cust1, cust2, cust3, cust4)

);

这样便可以在UNPIVOT查询中处理NULL值。

**别名和****UNPIVOT**

就像使用PIVOT关键字一样，我们可以使用UNPIVOT将别名应用于数据。但是，我们只能使用pivot\_in\_clause来执行此操作。例如：

SELECT \*

FROM pivoted\_sales

UNPIVOT (

total\_sales

FOR customer\_id

IN (

cust1 AS 'cust 1 sales',

cust2 AS 'cust 2 sales',

cust3 AS 'cust 3 sales',

cust4 AS 'cust 4 sales'

)

);

该查询的结果是：

customer\_id列中的值已转换为指定的值。它们需要包含在单引号中。如果不是，则会出现错误，如下所示：

SELECT \*

FROM pivoted\_sales

UNPIVOT (

total\_sales

FOR customer\_id

IN (

cust1 AS cust\_1\_sales,

cust2 AS cust\_2\_sales,

cust3 AS cust\_3\_sales,

cust4 AS cust\_4\_sales

)

);

ORA-56901：数据透视表| unpivot值不允许使用非常数表达式

56901. 00000-“枢轴值|非枢轴值不允许使用非常数表达式”

\*原因：尝试对枢轴值|非枢轴值使用非常数表达式。

\*操作：将常量用于数据透视|取消数据透视。

错误在行：339列：12

此ORA-56901错误（数据透视表值不允许使用非常数表达式）意味着您必须为别名使用常数值。您可以将别名括在单引号中以解决此错误。

**您可以不使用****Oracle PIVOT****进行汇总吗？**

不，SQL PIVOT仅适用于聚合函数。如果要在不丢失数据细节的情况下将行转置为列，可以尝试使用MAX或MIN函数。

假设您在bug\_result表中有一个错误列表，并且每个阶段都有不同的结果：

如果要在此数据集上使用PIVOT，则可以使用RESULT列上的MAX函数来完成。

SELECT \*

FROM bug\_result

PIVOT (

MAX(result)

FOR stage

IN ('Dev', 'Test', 'UAT')

);

MAX和MIN函数与VARCHAR2或文本值一起使用的效果与与日期和数字一样好。因此，如果在数据库上运行此查询，则会得到如下结果：

这样便可以在没有聚合的情况下在Oracle SQL中生成数据透视表。总结没有数字值的结果非常好。

**结论**

Oracle PIVOT和UNPIVOT功能强大，是转换数据的非常有用的方法。它们通常与数值一起使用以汇总数据，但也可以与文本和日期值一起使用。

它包含XML功能，可以XML格式导出数据。您还可以将别名添加到PIVOT行和列，添加WHERE子句，并执行多个聚合和组。

花一些时间练习使用此便捷的SQL功能，您将能够更好地处理数据。

[Link 1]: https://www.databasestar.com/oracle-sum/
[GROUP BY]: https://www.databasestar.com/sql-group-by/
[SELECT]: https://www.databasestar.com/sql-select-statement/
[Link 2]: https://www.databasestar.com/sql-concatenate/
[Link 3]: https://www.databasestar.com/oracle-sql-aggregate-functions/
[Link 4]: https://www.databasestar.com/sql-views/