微软BI 之SSIS 系列 - UNPIVOT

开篇介绍

在上一篇文章中讲解了在 SQL 和 SSIS 中使用 PIVOT 进行行列转换，在这一篇文章中讲解逆向的 UNPIVOT 操作。

注：本文都是 SQL Server 2012 版本，2008 R2 在 SSIS 中配置不同。

SQL UNPIVOT 操作

测试的源数据

要求转换之后的结果。

在 SQL 语句中，需要掌握这样的一个转换语法规则就可以非常容易的解决这个问题了。

/**

SELECT 非透视转换列，

    透视转换新列，

    透视转换数据新列

FROM(

  SELECT 非透视转换列，透视转换列1，透视转换列2...

  FROM 源数据表

 ) AS 别名

UNPIVOT

 （

  透视转换数据新列 FOR 

  透视转换新列 IN (透视转换列1，透视转换列2...)

  ) AS 别名

**/

• 非透视转换列 - 不需要转换的列，或者理解为轴，不移动的列。
• 透视转换新列 - 需要被新创建的列的名称，这个列的数据是描述转换前原有列的名称。即源表中1,2,3,4... 是原有列的名称，那么转换之后这 1,2,3,4 将变成透视转换新列的值，透视转换新列在这里我们可以取一个名字叫 SalesMonth。
• 透视转换数据新列 - 需要被新创建的存放 非透视转换列与透视转换新列 交叉时的数据；比如 Biycle 1月份的数据 4 就需要存放在这个列中。
• 透视转换列1,2 - 即需要将列名称转换并存放在 透视转换新列 中的那些列。

有了上面的这个语法，就非常容易实现这种 UNPIVOT的操作了。

SELECT ProductName,

       SalesMonth,

       SalesCount

FROM(

        SELECT ProductName,

               [1],

               [2],

               [3],

               [4],

               [5],

               [6],

               [7]

        FROM T041_PRODUCT_SALES

    ) AS SRC

UNPIVOT

    (

        SalesCount FOR

        SalesMonth IN ([1], [2], [3], [4],  [5],  [6], [7])

    )AS UNP 

还有一点也要注意：UNPIVOT 不完全是 PIVOT 的逆操作；因为在 PIVOT 中我们可以通过聚合函数将多行的数据聚合再转换，但是 UNPIVOT 是不能知道聚合值该如何还原成细节行数据的。

SSIS 中的 UNPIVOT 操作

SSIS 中的 UNPIVOT 操作也非常简单，只要上一篇文章中的 PIVOT 操作弄清楚了，那么这个 UNPIVOT 的配置就会容易很多，只需要理解对应的配置选项即可。

在数据流中拖放 UNPIVOT 控件。

在 UNPIVOT 的配置中：

• 非透视转换列 - ProductName 是不需要转换的。
• 透视转换新列 - Pivot key value column name，这里是 SalesMonth。
• 透视转换数据新列 -  交叉数据的存放位置 - SalesCount
• 透视转换列1,2 - Input Column 中被选中的即是。

运行执行中观察到的结果。