SSIS的透视(Pivot)和逆透视(UnPivot)

　　导言

　　数据的表现形式要求能够易于分析。将列转成行而行转成列是数据表现的另一种方式，以便终端用户可以很容易地理解。除了表现目的，你还需要将你在数据仓库应用程序之中的数据转换成不同的数据格式。这个过程可以称之为‘透视(Pivot)’，而这个过程的反向则称之为‘逆透视(Unpivot)’。

　　在这篇文章里，我们将讨论你在SQL Server集成服务(SSIS)中可以怎样使用透视和逆透视处理。

　　必要条件

　　因为这篇文章将告诉你怎样编写SSIS包，所以你需要具有一定的创建SSIS包的经验。不过我将努力讨论创建SSIS包的细节，无论其是否必要，但是并不打乱这篇文章的主题。此外，有必要知道怎样编写一个连接三个或更多表的查询。

　　软件要求安装SQL Server 2005和SQL Server 2005商业智能开发套件。这篇文章假设你在使用Adventureworks 数据库的数据。所以读者最好和SQL Server 2005数据库服务器一起安装了Adventureworks 数据库。

　　这个练习使用的数据文档可以从这里找到。

　　透视

　　要理解什么是透视，让我们看一个例子。下面显示了Advenureworks 数据库中Sales.SalesOrderHeader、Sales.SalesOrderOrder、Prodcution.Product和Production.ProductCategory表的关系。

　　从上面的关系图中，我们可以假设我们需要下面的输出。

　　产品

　　将上面的表调整为所要求的格式就是一个“透视”例子。

　　让我们从设计包开始。首先，在创建集成项目后添加一个新的包。

　　然后添加一个数据流任务来控制流。然后，添加一个OLE DB 连接并配置这个OLE DB 连接到Adventureworks 数据库。

　　最后，添加一个OLE DB源到所添加的数据流。我们要设计SSIS包使用最少的代码，同时我们将使用尽可能多的SSIS包。因此，我们将添加下面的代码到所添加的OLE DB源。

以下是引用片段：
　　SELECTPC.Name
　　,soh.OrderDate
　　,SOD.OrderQty
　　FROMSales.SalesOrderDetailSOD
　　INNER
　　JOINSales.SalesOrderHeaderSOH
　　ONSOH.SalesOrderID=SOD.SalesOrderID
　　INNER
　　JOINProduction.ProductPROD
　　ONProd.ProductID=SOD.ProductID
　　INNERJOINproduction.ProductCategoryPC
　　ONPROD.ProductSubcategoryID=PC.ProductCategoryID

　　在添加了上面的代码之后，OLE DB源将如同下面的截屏所示。

　　我们将添加一个条件分割，因为我们只对‘年’做透视。要提高可用性，我采用了一个叫做‘intYear’的变量。因此，如果你想改变年，那么只要改变变量‘intYear’的值就可以了。

　　下面是这个条件分割的一个截屏。

　　在上面的例子中，@User::intYear的值设置为2001。

　　接下来，我们需要获得季度值。尽管我们可以修改原始的T-SQL 来返回这个季度值，但是我使用了获取字段数据流转换任务。下面的表达式是用来获得这个季度值的。

以下是引用片段：
　　MONTH(OrderDate)>=1&&MONTH(OrderDate)<=3?1:MONTH(OrderDate)>=4&&MONTH(OrderDate)<=6?2:MONTH(OrderDate)>=7&&MONTH(OrderDate)<=9?3:MONTH(OrderDate)>=10&&MONTH(OrderDate)<=12?4:0

　　现在我们需要以种类和季度为上面的数据分组。我们可以使用聚合转换和配置它按照Name和intQtr分组。

　　接下来我们需要添加一个排序转换，在这里我使用种类来排序。我们还需要排序键字段，否则透视将不能正常工作。要查看它的数据，你可以添加一个数据阅览器。

　　下面是要获得的数据集截屏，它是我们需要进行透视的数据集。

　　我们现在达到了本篇文章的核心部分——透视。对于透视，有一个透视转换配置，它不是非常直接的。在透视转换的输入标签中，你需要选择你要在透视操作中使用的字段，在这个例子中是所有的三个变量字段。

　　接下来最重要的标签是‘Input and Output’属性标签，如下图所示。

　　对于输入字段，我们需要配置透视使用属性。

　　来源：SQL Server 2005在线书籍

　　根据上面的表，Name字段应该是选项1、intQtr 应该是选项2、而OrderQty 应该是选项3作为透视使用属性值。

　　下一步是配置输出字段。

　　点击添加字段按钮，ProductCategory、Qtr1、Qtr2、Qtr3和Qtr4会被添加进来。我们需要为这四个字段配置SourceColumn和PivotKeyValue 属性。

　　对于ProductCategory字段，分配Name输入字段的LineageID到SourceColumn。在这个例子中，它是1161。对于Qtr1字段，分配OrderQty输入字段的LineageID到SourceColumn，在这个例子中是1161。PivotKeyValue是初始的数据集合的字段值。对于Qtr1，我们需要intQtr值为1的记录。所以Qtr1的PivotKeyValue是1。

　　类似的，Qtr2的SourceColumn是1166，PivotKeyValue是2。Qtr3的SourceColumn是1166， PivotKeyValue是3。Qtr4的SourceColumn是1166，PivotKeyValue是4。

　　在这里我们还添加了另一个透视转换之后的数据阅览器。下面是这个数据阅览器的输出，这样就达到了我们的目的。

　　下图描述了这个包的最终输出。

　　我们可以扩展这个包从季度查看月份。为此，我们需要改变这个获取字段转换。下面显示了它的做法。

以下是引用片段：
　　MONTH(OrderDate)==1?"Jan":MONTH(OrderDate)==2?"Feb":MONTH(OrderDate)==3?"Mar":MONTH(OrderDate)==4?"Apr":MONTH(OrderDate)==5?"May":MONTH(OrderDate)==6?"Jun":MONTH(OrderDate)==7?"Jul":MONTH(OrderDate)==8?"Aug":MONTH(OrderDate)==9?"Sep":MONTH(OrderDate)==10?"Oct":MONTH(OrderDate)==11?"Nov":MONTH(OrderDate)==12?"Dec":"UNK"

　　这个排序是为排序转换中的Month字段所做的。

　　下一个关键配置是透视转换的输入输出配置。我们将需要独立于Product Category字段来添加Month的所有十二个字段。

　　这个配置和第一个例子中的类似。例如，Jan字段的PivotKeyValue是Jan，SourceColumn 是输出字段OrderQty 的LinegeID。

　　下面显示了这个包的最终输出。

　　在这两个例子中，我们使用一个ProductCategory字段进行透视。现在我们将试图添加两个字段进行透视——Year和Month。

　　我们将需要添加Year 字段，获取字段采用的是YEAR(OrderDate)。Year字段包含进来是用于聚合转换的。对Month和Year进行了分类。

　　让我们看看我们要为透视转换做些什么。intYear字段的PivotUsage是1。Year的源字段输出字段是intYear的LineageID 输入字段。

　　下面显示了上面的包的输出。

　　逆透视

　　既然我们已经学习了透视，接下来该学习怎样进行‘逆透视’了。逆透视转换通过扩展一个单独的记录中多个字段的值到一个单独字段相同值的多个记录中去，从而将一个反正规化的数据集转换为一个更加规格化的版本。它和透视正好相反。

　　我们可以从我们之前的数据格式开始，它们是采用逗号分离值(csv)格式的。

　　2002,Accessories,,,,,,,204,353,313,117,166,128

　　2003,Accessories,51,75,80,100,138,156,215,381,386,120,131,188

　　2004,Accessories,40,76,75,91,188,178,,,,,,

　　2001,Bikes,,,,,,,148,519,383,213,660,492

　　2002,Bikes,206,516,445,234,635,500,751,1088,1005,646,968,881

　　2003,Bikes,580,887,680,746,937,753,876,1312,1333,878,1234,1332

　　2004,Bikes,758,1084,1033,940,1332,1336,,,,,,

　　2003,Clothing,,,,,,,890,1474,1700,829,1254,1610

　　2004,Clothing,732,1018,1447,890,1301,1606,,,,,,

　　2001,Components,,,,,,,453,764,575,667,1258,1007

　　2002,Components,618,1161,862,738,1282,946,1865,2526,1782,1468,2229,1556

　　2003,Components,1176,2029,1286,1454,2283,1485,1225,1562,1311,1082,1436,1408

　　2004,Components,965,1374,1170,1217,1597,1379,,,,,,

　　你可以从一个示例包中叫做PivotedData.txt的文本文件中获得上面的数据。

　　首先，我们将添加一个包到现有的SSIS解决方案中去。然后我们将添加一个平面文件连接管理器并将pivoteddata.txt文件指向它。最后，我们将添加一个平面文件源并将之前添加的平面文件连接管理器指向它。下面显示了你应该从平面文件源看到的数据输出。

　　我们下一步是进行逆透视。与透视配置不同，逆透视配置相对简单。

　　你将需要选择透视字段，在这个例子中透视字段是Jan、Feb、Mar、Apr、May、Jun、Jul、Aug、Sep、Oct、Nov和Dec。透视字段名称将出现在标题为Month的字段中，你可以在屏幕底部配置它。这些pivotedkey字段(Jan、Feb等等)的值放在叫做Qty的字段中，你可以在目标字段中配置它。

　　在这个逆透视例子中有一个小问题。在这里，在Qty字段里具有“0”值的字段将被转换到记录中去。例如，在2002年1月accessories 的Qty 是‘0’，这使得将有一个对于这个记录来说不必要的记录。要减少这些记录，就要使用一个具有简单条件的条件分割。条件是Qty > 0，这将除去所有在Qty字段中具有“0”值的记录。

　　下面是逆透视转换的输出。

　　下图描述了最终的逆透视包。

　　示例SSIS包

　　总结

　　透视和逆透视转换是使你可以改进你的数据表达的重要控制。以最少的时间和知识，你可以成功地改变你的数据表达。