SSIS 中的透视转换

SSIS 中的透视转换 (Pivot Transformation) 执行输入数据 (源数据) 的透视操作。SSIS 中的透视转换意味着将单个行数据转换为单独的列。

我们使用 SSIS 透视转换以交叉表或表格格式呈现数据，以便更好地理解。它将执行基于行的操作，并将行旋转到列，并将相应的值放在这些列下。如果没有值，它将用 NULL 填充。

在这里，我们将通过一个简单的例子来解释 SSIS 中的透视转换，以便您更好地理解。

透视转换正好与逆透视转换相反。在我们之前的文章中，我们已经讨论了 SSIS 2008R2 中的透视转换。虽然 SSIS 2008R2 和 2014 的功能相同，但 Microsoft 在 SQL Server 2012 中更改了透视转换的用户界面。这个修改后的版本比早期版本让开发者的生活轻松了很多。请参考 2008R2 部分的透视转换，以了解 2014 和 2008R2 中透视转换的区别。

注意：这并非强制要求，但对于未排序的数据，在透视转换之前使用排序转换始终是最佳实践。因为有时 SSIS 透视转换对于未排序的数据会产生奇怪的结果。

SSIS 中的透视转换示例

在此 SSIS 示例中，我们将对下表执行透视转换，将行数据（如日历年 2011、2012、2013 和 2014）转换为单独的列。

在此示例中，请考虑以下数据。

步骤 1：打开 BIDS，将数据流任务从工具箱拖到控制流，并将其重命名为 SSIS 2014 中的透视转换。

双击它，它将打开数据流选项卡。有关更多转换 >> 点击此处。

步骤 2：将 OLE DB 源、透视转换从工具箱拖到数据流区域。

步骤 3：双击数据流区域中的 OLE DB 源，将打开连接管理器设置，并提供编写语句的空间。

用于 SSIS 透视转换的 SQL 命令是：

SELECT PROD.ProductID,
       PROD.Name,
       YEAR(OrdHead.OrderDate) as OrderYear,
       SUM(Details.OrderQty) AS OrderQuantity
FROM Sales.SalesOrderDetail AS Details
     INNER JOIN
       Production.Product AS PROD ON
          Details.ProductID = PROD.ProductID
     INNER JOIN 
       Sales.SalesOrderHeader AS OrdHead ON
         Details.SalesOrderID = OrdHead.SalesOrderID
GROUP BY PROD.ProductID,
         PROD.Name,
         YEAR(OrdHead.OrderDate)
ORDER BY PROD.Name, 
         YEAR(OrdHead.OrderDate)

步骤 4：单击“列”选项卡以验证列。在这里，我们也可以取消选中不需要的列。

单击“确定”，然后拖动 OLE DB 源的输出箭头到透视转换，以对源数据执行透视转换。

配置 SSIS 透视转换

步骤 5：双击或编辑透视转换。它将打开透视窗口，这与 SSIS 2008R2 中的透视转换略有不同。

下表说明了您可以在 SSIS 透视转换编辑器中使用的值：

选项	描述
0	列值将传递给转换。
1	设置键。所有具有相同设置键的列。
2	这是透视键 (这些列值将成为列名)。
3	这是透视值，该列中的值将放置在透视转换创建的新列中。

因此，在此 SSIS 透视转换示例中：

ProductID = 0

Name = 1 (设置键)

OrderYear = 2 (透视键)

OrderQuantity = 3 (透视值)

单击“确定”并运行 SSIS 透视转换包。转到“进度”选项卡，检查列名，如下图所示。

注意：对于像本例中的 3 或 4 列，我们可以直接用列名替换 [Value1]、[Value2] 等，但在实际操作中，我们可能会处理 50 年的数据，那么在“生成透视输出列值”区域编写这 50 年的名称是不可能的。因此，最好运行包一次，然后从“进度”选项卡复制列名。

停止调试模式并复制选定的值。

生成列

再次双击数据流区域中的 SSIS 透视转换，并将列名粘贴到“从值生成透视输出列”区域，如下图所示。

单击“立即生成列”按钮以生成透视列。这些生成的列会自动添加到“现有透视输出列”区域。

单击“生成输出列结果”窗口中的“确定”以生成如上屏幕截图所示的输出列。

从上图可以看出，“现有透视输出列”区域为源数据生成了输出列 (C_2011_OrderQuantity、C_2012_OrderQuantity、C_2013_OrderQuantity 和 C_2014_OrderQuantity)。

单击“确定”。

现在，我们需要将输出列保存在数据库中。因此，将 OLE DB 目标从工具箱拖到数据流区域。接下来，将 SSIS 透视转换的输出箭头拖到 OLE DB 目标。

双击 OLE DB 目标，并提供所需信息，例如 SQL Server 名称、数据库和目标的表详细信息。

在这里，我们选择了数据库作为目标数据库，并选择了 [SSIS 中的透视转换] 表作为目标表。

单击“映射”选项卡以检查透视转换源列是否已映射到目标列。

Mapping available Input and Destination Columns

注意：如果您的输入和目标列名相同，智能映射会自动完成。如果列名有任何更改（别名或计算列），我们必须手动进行映射。

从上图可以看出，智能映射无法独立完成。因此，请正确映射。

单击“确定”以完成 SSIS 透视转换包的设计。让我们运行该包。

透视结果

让我们打开 SSMS 并编写以下查询以检查透视转换结果。

SELECT [Product Name]
     ,[2011]
     ,[2012]
     ,[2013]
     ,[2014]
FROM [PIVOT Transformation]

SSIS 2008 R2 中的透视转换

透视转换允许您对输入数据执行透视操作。在这里，我们将通过一个简单的例子来解释 SSIS 2008R2 中的透视转换，以便您更好地理解。

场景：我们希望使用 SSIS 2008 R2 中的透视转换来透视下表，以获得期望的输出。

打开 BIDS，将数据流任务从工具箱拖到控制流，并将其重命名为 SSIS 2008 r2 中的透视转换。

双击它，将打开 SQL Server Integration Services 的数据流选项卡。

将 OLE DB 源、透视转换和 OLE DB 目标从工具箱拖到数据流区域。

双击数据流区域中的 OLE DB 源，将打开连接管理器设置，并提供编写语句的空间。

我们将用作 SSIS 透视转换源的 SQL 命令是：

SELECT PROD.ProductID,
       PROD.Name,
       YEAR(OrdHead.OrderDate) as OrderYear,
       SUM(Details.OrderQty) AS OrderQuantity

FROM Sales.SalesOrderDetail AS Details
     INNER JOIN
       Production.Product AS PROD ON
          Details.ProductID = PROD.ProductID
     INNER JOIN 
       Sales.SalesOrderHeader AS OrdHead ON
         Details.SalesOrderID = OrdHead.SalesOrderID
GROUP BY PROD.ProductID,
         PROD.Name,
         YEAR(OrdHead.OrderDate)
ORDER BY PROD.Name, 
         YEAR(OrdHead.OrderDate)

单击列选项卡以验证列。在此选项卡中，我们也可以取消选中不需要的列。

单击“确定”并编辑 SSIS 2008 R2 中的透视转换。在“组件属性”选项卡中，我们可以更改名称，但暂时保留原样。

转到“输入列”选项卡，选择从 OLE DB 源接收的所有输入列。

转到 SSIS 透视转换的输入和输出属性选项卡，选择“透视默认输入”。在“输入列”下，您将找到在前一个选项卡中选择的所有列。您必须为每个输入列设置的唯一属性是 PivotUsage。

它描述了您可以在 PivotUsage 属性中使用的值：

选项	描述
0	列值将传递给转换。
1	设置键。所有具有相同设置键的列。
2	这是透视列（这些列值将成为列名）。
3	此列中的值放置在透视转换创建的新列中。

因此，在此示例中，每个输入列的 PivotUsage 属性将如下所示：