微软BI 之SSIS 系列 - 在 SQL 和 SSIS 中实现行转列的 PIVOT 透视操作 - BIWORK

微软BI 之SSIS 系列 - 在 SQL 和 SSIS 中实现行转列的 PIVOT 透视操作

2015-01-01 10:49 BIWORK 阅读(5031) 评论(6) 编辑收藏举报

开篇介绍

记得笔者在 2006年左右刚开始学习 SQL Server 2000 的时候，遇到一个面试题就是行转列，列转行的操作，当时写了很长时间的 SQL 语句最终还是以失败而告终。后来即使能写出来，也是磕磕碰碰的，虽然很能锻炼 SQL 功底，每次都要挣扎一番，溺水的感觉。记得SQL Server 2005 以后就有了 PIVOT 和 UNPIVOT 这两个函数，可以非常方便的实现行转列和列传行的操作，就不再那么挣扎了。后来，在一个 08 项目中，有一位新的女同事在改一个 ETL，发现 SSIS 包中有一个PIVOT 控件不知道怎么用就叫我帮忙。虽然我觉得花点时间还是可以搞定的，但是为了赶回家看一场球赛，找了一个不靠谱的接口就扔下她一个人给跑了。因为项目应该很急，每个人压力其实都很大，不记得是当天晚上就要交付还是第二天要交付。现在想想，很内疚也非常败人品，因为平时大家伙还都比较信任我，但是关键时刻跑了，确实有点不太负责任。今天正好整理到这一部分的笔记就想到了这个疙瘩，山东的那位妹子如果看到了，说声对不起吧！

SSIS 笔记整理到这几个地方，就来总结一下 PIVOT 的使用，如果之前不会用的，看了这篇文章就可以明白了。

测试代码

IF OBJECT_ID('T040_PRODUCT_SALES') IS NOT NULL
DROP TABLE T040_PRODUCT_SALES
GO
CREATE TABLE T040_PRODUCT_SALES
(
   ID INT IDENTITY(1,1),
   ProductName VARCHAR(20),
   SaleMonth INT,
   SalesCount INT
)
 
-- Inserting test data
INSERT INTO T040_PRODUCT_SALES VALUES
('Bicycle',1,1),
('Shoes',2,2),
('Clothes',3,3),
('Books',4,4),
('Medicine',5,5),
('Drinks',6,6),
('Shoes',7,7),
('Books',1,2),
('Bicycle',1,3),
('Medicine',1,4),
('Clothes',1,5),
('Mobile Phone',1,6),
('Books',1,7),
('Medicine',1,8),
('Shoes',1,9),
('Bicycle',2,10)
SELECT ProductName,
    SaleMonth,
    SUM(SalesCount) AS SalesCount
FROM T040_PRODUCT_SALES
GROUP BY ProductName,
   SaleMonth
ORDER BY ProductName,
      SaleMonth

我们需要实现的效果是按产品名称，1月，2月，3月，4月，5月，6月这七个列来显示 SalesCount 的总数。

怎么来实现这种行列转换效果，只要把下面这个点就理解清楚，照着写就可以实现。

/****
SELECT 非透视列,
             [透视列 1] AS '列名1',
             [透视列 2] AS '列名2',
             [透视列 3] AS '列名3'
FROM (
        -- 源数据
        SELECT 非透视列,
               透视列值的来源列,
               需要聚合的值
        FROM 表
     )AS 别名
PIVOT
     (
        SUM(需要聚合的值)
        FOR 透视列值的来源列 IN ([透视列 1]，[透视列 2]，[透视列 3])
     )AS 别名
****/

对照上面的语法，我们弄清楚这些对应关系：

非透视列 - 一般是第一列，把效果想出来，ProductName 就是位于第一列，它是非透视列。
透视列 - 就是需要由列变为行的那些列，哪些行中的值需要作为列来显示？ 1月 - 6月。
透视列值的来源列 - 就是 SaleMonth，这列包含了 1月 - 6月的值。
需要聚合的值 - 就是 SalesCount。

把这些需求理解了，就直接按照上面的这个语法就可以实现了，没有一点点多余的代码。

SELECT ProductName,
    ISNULL([1],0) AS '1',
    ISNULL([2],0) AS '2',
    ISNULL([3],0) AS '3',
    ISNULL([4],0) AS '4',
    ISNULL([5],0) AS '5',
    ISNULL([6],0) AS '6'
FROM(
 SELECT ProductName,
     SaleMonth,
     SalesCount
 FROM T040_PRODUCT_SALES
)AS Sales
PIVOT
(
 SUM(SalesCount)
 FOR SaleMonth IN([1],[2],[3],[4],[5],[6])
)AS PIVOTBL