本文解释了SSIS透视转换以及它与unpivot转换的区别。
本文是SSIS功能面对面系列的第十三篇文章，该系列旨在消除混淆，并说明SQL Server集成服务（SSIS）提供的类似功能之间的一些区别。
简介
一般来说，"Pivot "这个词的意思是指一个轴或一个针，东西在上面转动。在数据的世界里，数据透视是一种数据处理技术，通过将行转换成列来重塑表格数据。透视一般用于数据分析和可视化。Unpivot是pivot的反向操作，其中列被转换为行。
图1--透视与解透视的操作
在SQL Server中，有两个内置的关系运算符，叫做Pivot和Unpivot，可以用来对关系数据进行透视和取消透视操作。由于在数据导入阶段有时需要这两个操作，所以在SQL Server集成服务中开发了两个转换。下面的章节将解释SSIS透视转换和SSIS取消透视转换以及如何在数据导入阶段使用它们。
- ***题外话：*本文的所有例子都是使用Stack overflow 2013数据库、SQL Server 2019、Visual Studio 2019、SQL Server Integration Services项目扩展3.4版制作的。
正如Visual Studio工具箱中所描述的那样，SSIS透视转换*"通过对某一列值进行透视来压缩输入数据流，使其不那么规范化"。* 此外，还提到了两个基本要求。
- "输入的数据应该按照透视列进行排序，因为每次透视列中的数据发生变化时都会发生透视。"这条说明意味着，如果数据没有被排序，这可能会导致组件输出元数据的无意义变化，导致失败
- "重复的行将导致该转换失败。"因 为重复的列'将在组件输出中产生
图2 - SSIS工具箱中的透视转换描述
让我们考虑下面的查询，它返回每年获得的徽章数量。
SELECT [姓名] ,YEAR([Date]) as Earned_year ,COUNT([UserId]) as Time_Earned FROM [StackOverflow2013].[dbo].[徽章]。 GROUP BY [Name],YEAR([Date]) ORDER BY [Name], Earned_year |
图3 - 每年获得的徽章
为了解释SSIS透视转换，我们（1）创建一个集成服务项目，（2）添加一个包，（3）创建一个OLE DB连接管理器来配置与Stack Overflow 2013数据库的连接。
在包的控制流中，我们添加了一个数据流任务。如下图所示，我们添加了一个OLE DB Source，并将其配置为从上面提到的SQL命令中读取。
图4 - OLE DB源配置
现在，让我们添加一个将OLE DB Source输出作为输入的透视转换。在SSIS Pivot转换编辑器中，我们需要选择以下列。
图 5 - 透视转换编辑器
- Pivot Key:该列的输入数据中的值将成为输出中的新列名。在这个例子中，我们应该选择 "Earned_Year "列。
- 集合键。确定一组输入行，将被透视为一个输出行
- 枢轴值。该 列的值将被映射到新的透视输出列中。
图6 - 透视列解释
由于SSIS组件元数据需要在执行前定义，所以应该定义透视关键列。如果我们已经知道数据中的值，我们应该把它们写在值文本框中，然后点击 "现在生成列 "按钮，如下图所示。
图7 - 生成输出列
如果我们不知道数据中的所有值，我们应该勾选 "忽略未匹配的Pivot Key值并在DataFlow执行后报告它们 "选项。在调试器中执行DataFlow，并复制调试器输出窗口中报告的值列表（这在SSIS透视转换编辑器中作为提示提到）。
图8 - 忽略未匹配的Pivot键选项
要执行数据流任务，进入包控制流，在数据流任务上按下右键，然后点击 "执行任务"。
图 9 - 执行数据流任务
执行完成后，进入 "进度 "选项卡，检查SSIS透视转换的输出，如下图所示。
图10 - 阅读透视转换输出
你可以通过在信息上按下右键并点击 "复制信息文本 "来复制这个输出。
图11 - 复制透视转换输出
我们应该只把列名粘贴到输出列文本框中，然后点击 "现在生成列"。我们可以在 "现有的透视输出列 "文本框中检查添加的列。
图 12 - 生成输出列
在配置了SSIS透视转换后，我们将把结果导出到一个平面文件（CSV）中。如下图所示，行的数量被减少到在 "Set Key "列中发现的不同值的数量。
图13 - 执行的数据流任务
如果我们打开创建的CSV文件，我们可以检查数据是如何被转化为一个透视表的。
图14 - 输出的CSV文件的数据样本
SSIS取消透视转换
正如SSIS工具箱中提到的，unpivot转换*"将未规范化的数据流扩展为更规范化的版本（从单一记录的多列数值扩展为单一列的多条记录）"。*
图15 - SSIS工具箱中的unpivot转换描述
为了解释SSIS的unpivot转换，我们将添加一个新的数据流任务，它有一个平面文件源，从我们在前面的例子中创建的CSV文件中读取。然后我们将添加一个SSIS unpivot转换组件，将平面文件源的输出作为一个输入。
现在，让我们打开SSIS unpivot转换编辑器。如下图所示，我们应该对这个转换进行如下配置。
- 我们应该为那些不需要取消透视的列勾选 "通过 "选项。
- 我们应该检查所有需要取消透视的列
- 在列网格中，我们应该为每个输入列指定一个输出列的名称。在这个例子中，我们使用 "Times_Earned "作为列名，因为它被用于我们使用的初始SQL查询中。
- 我们应该指定枢轴值的输出列名，这些值将被储存在那里。在这个例子中，我们使用 "Earned_Year "作为列名，因为它最初是在本文中使用的。
图16 - SSIS的unpivot转换编辑器
在配置了unpivot转换后，我们添加了一个Flat文件目标，以将unpivoted数据导出到另一个Flat文件（CSV）。执行软件包后，我们应该注意到unpivot转换的输出行数比输入行数要多。
图17--Unpivot的输入和输出行数
如果我们打开创建的CSV文件，我们可以检查被透视的数据是如何被转换回正常化的表格的。
图18 - 解除透视的数据样本
- ***重要提示：**unpivot转换的输出是13530行，而SSIS pivot转换的输入是4979行。造成这种差异的原因是，unpivot转换为每个枢轴键，设置键的组合生成了一行，而在初始数据中可能不存在。例如，在初始数据中，".htaccess "名称中没有2008年的行，而在unpivot输出中却有一个等于0的值。**unpivot转换的输出行数=设定键值的数量x枢纽列的数量。*在这个例子中：13530（输出行数）=2255（姓名不同的值）x 6（收入年份的值）。
总结
在这篇文章中，我们简要地解释了透视和不透视的操作，以及为什么开发人员要使用它们。然后，我们解释了SSIS透视转换，以及如何配置它将归一化数据转换为透视表。 最后，我们解释了SSIS取消透视转换，以及如何将透视的数据转换为归一化数据。
内容列表
Hadi是一名拥有超过10年经验的SQL Server专业人士。他的主要专长是数据集成。他是Stackoverflow.com上的顶级ETL和SQL Server集成服务贡献者之一。此外，他还发表了几篇关于Biml、SSIS功能、搜索引擎、Hadoop和许多其他技术的系列文章。
除了使用SQL Server，他还使用过不同的数据技术，如NoSQL数据库，Hadoop，Apache Spark。他是MongoDB、Neo4j和ArangoDB的认证专家。
在学术层面，Hadi拥有计算机科学和商业计算的两个硕士学位。目前，他是数据科学的博士生，专注于大数据质量评估技术。
哈迪非常喜欢每天学习新事物并分享他的知识。你可以在他的个人网站上找到他。
