SQL Server PIVOT

本文将完整概述如何在 SQL Server 中使用 PIVOT 和 UNPIVOT 运算符。PIVOT 和 UNPIVOT 运算符类似于关系运算符，它们允许将表值表达式转换为另一个表。这两个运算符都生成多维报告，有助于快速组合和比较大量数据。

当我们需要转换表值表达式时，可以使用 PIVOT 运算符。它将一列中的唯一值拆分为最终结果中的多列。它还会聚合最终结果所需的其余列值。UNPIVOT 运算符将表值表达式中的列数据转换为列值，这与 PIVOT 相反。

让我们通过下面的简单图表来理解它

在这张图的左侧，我们可以看到原始数据集，它有三列：**年、区域**和**销售额**。接下来，在右侧，我们可以看到 PIVOT 表，它是通过将**区域（行）旋转为北方和南方（列）**构建的。将行转换为列后，我们可以对 PIVOT 表的列和行之间每个交点的**销售额列值进行聚合**。

让我们首先创建一个名为 pivot_demo 的表来演示 PIVOT 和 UNPIVOT 运算符。以下语句在我们的指定数据库中创建一个新表

接下来，按照如下方式向该表插入一些数据

我们可以使用 SELECT 语句验证数据。我们将得到以下输出

PIVOT 运算符

此运算符用于旋转表值表达式。它最初是在 SQL Server 2005 版本中引入的。它将数据从行转换为列。它将一列中的唯一值拆分为多列，然后聚合最终结果所需的其余列值。

我们必须遵循以下步骤来创建 PIVOT 表

• 选择用于透视的基础数据集。
• 借助派生表或 CTE（公共表表达式）创建临时结果。
• 使用 PIVOT 运算符。

语法

以下语法说明了 SQL Server 中 PIVOT 的使用

如果我们分解这个脚本，我们可以看到它有两个独立的部分。第一部分从主表中选择数据，第二部分确定 PIVOT 表的构建方式。第二部分还包含一些特殊关键字，如 SUM、FOR 和 IN。让我们看看 PIVOT 运算符中这些关键字的含义。

SUM

此运算符用于聚合指定列中的值，以便在 PIVOT 表中使用。我们必须将其与 PIVOT 运算符一起使用，以获取值部分的聚合列显示。

FOR 关键字

此关键字用于 PIVOT 表语句，以指示 PIVOT 运算符应将 PIVOT 函数应用于哪个列。基本上，它表示将从行转换为列的列名。

IN 关键字

此关键字列出了 PIVOT 列中的所有唯一值，以便作为 PIVOT 表的列显示。

示例

让我们通过各种示例来理解它。

1. 以下语句首先选择 Year、North 和 South 列作为透视的基础数据。然后，使用派生表创建临时结果，最后应用 PIVOT 运算符生成最终输出。此输出也按升序 Year 排序。

执行此语句将产生以下输出。在这里，我们可以看到对应年份的北方和南方区域销售额的计算总和。

2. 这是另一个示例，我们将计算每个年份对应区域销售额的总和

执行此语句将产生错误，因为我们不能直接将数值指定为列名。

但是，SQL Server 允许我们通过在每个整数值之前使用括号来避免此问题。更新后的语句显示在以下代码片段中

此语句成功执行并显示了每个年份对应区域销售额的计算总和

3. 前面获取 PIVOT 表的示例在已知所有可能的 PIVOT 列值时很有用。但假设在未来一年中，列的数量增加了。考虑前面的示例，我们有 2010、2011 和 2012 年作为 PIVOT 列。然而，不能保证这些列将来不会改变。如果我们在 2013 或 2014 年，甚至更多年份有数据会发生什么？在这种情况下，我们将需要使用动态 PIVOT 表查询来解决此问题。

动态 PIVOT 表查询将整个 PIVOT 脚本封装在一个存储过程中。此过程将提供可调整选项，允许我们通过更改一些参数化值来修改我们的需求。

以下 SQL 代码解释了动态 PIVOT 表的工作原理。在此脚本中，我们首先从 PIVOT 列中检索所有不同的值，然后编写 SQL 语句以便在运行时与 PIVOT 查询一起执行。让我们看看执行此脚本后的输出

在此脚本中，我们创建了两个参数化变量。其描述如下

@PivotColumn：此变量将从创建 PIVOT 表的原始表中获取列名。**例如**，此处，“Region”列显示列中所有可用的区域。

@PivotList：此变量将获取我们希望在 PIVOT 表中作为输出列显示的列列表。

动态存储过程的执行

成功创建动态存储过程后，我们就可以执行它了。以下语句用于调用动态存储过程以在运行时显示 PIVOT 表

现在，我们将列名“Region”指定为第一个参数，并将 PIVOT 列列表指定为第二个参数。执行脚本将显示以下输出

现在，我们可以在运行时将来添加更多列以显示 PIVOT 表，这在前两个示例中是不可能的。

UNPIVOT 运算符

它是 SQL Server 中 PIVOT 运算符的逆方法。这意味着此运算符通过将数据从列转换为行来执行 PIVOT 的相反操作。UNPIVOT 运算符还将 PIVOT 表旋转为常规表。它最初是在 SQL Server 2005 版本中引入的。

语法

以下语法说明了 SQL Server 中的 UNPIVOT

示例

让我们通过示例了解如何对 PIVOT 操作进行 UNPIVOT。我们将首先创建一个原始表和 PIVOT 表，然后在此表上应用 UNPIVOT 运算符。

以下代码片段首先声明一个临时表变量 @Tab

接下来，我们将按如下方式向此表插入值

现在，我们可以使用以下语句执行 UNPIVOT 操作

执行代码片段将返回以下输出

以下代码片段是另一个示例，用于在单个查询中首先执行 PIVOT 操作，然后对同一表执行 UNPIVOT 操作

执行代码片段将显示相同的输出

注意：UNPIVOT 过程是 PIVOT 过程的逆操作，但它不是精确的逆转。因为当 PIVOT 计算聚合并将多行合并到结果中的单行时，行已被合并，因此 UNPIVOT 操作无法使表与原始表相同。但是，如果 PIVOT 运算符没有将多行合并到单行中，那么 UNPIVOT 运算符可以从 PIVOT 输出中获取原始表。

结论

本文将完整概述 SQL Server 中的 PIVOT 和 UNPIVOT 运算符，并将表表达式转换为另一个表达式。永远不应忘记 UNPIVOT 是 PIVOT 的逆操作，但它不是 PIVOT 结果的精确逆。