pivoting&Unpivoting

Pivoting技术是指一种可以把行转换为列的技术，Unpivoting可以看作是pivoting的反向操作，即将列旋转成为行

首先做一张测试表

create table formatting(
orderid int not null,
orderdate date not null,
empid int not null,
custid vahrchar(10) not null,
qty int not null,
primary key (orderid,orderdate));

再插入一些测试数据：

insert into formatting select 1,'2010-01-02',3,'A',10;
insert into formatting select 2,'2010-04-02',2,'B',20;
insert into formatting select 3,'2010-05-02',1,'A',30;
insert into formatting select 4,'2010-07-02',3,'D',40;
insert into formatting select 5,'2011-01-02',4,'A',20;
insert into formatting select 6,'2011-01-02',3,'B',30;
insert into formatting select 7,'2011-01-02',1,'C',40;
insert into formatting select 8,'2009-01-02',2,'A',10;
insert into formatting select 9,'2009-01-02',3,'B',20;

此时可以将表formatting看作是一张汇总表，比如网上商城的购物明细。
这份汇总表显示了订单号，订单日期，员工编号，消费者编号和订单数量。

要在此汇总表的基础上进一步统计每个消费者每年的订单数量，可能会想到用分组来获得结果，比如：

 

select custid,year(orderdate) year,sum(qty) sum
from formatting
group by custid,year(orderdate);

结果为：


这样子并不直观，如果可以通过旋转得到这样的结果，那就直观和清晰多了：

select custid,
        ifnull(sum(case when a=2009 then qty end),0) \"2009\",
        ifnull(sum(case when a=2010 then qty end),0) \"2010\",
        ifnull(sum(case when a=2011 then qty end),0) \"2011\"
from (select custid,year(orderdate) a,qty
    from formatting) p
group by custid;

 

但是当旋转的元素非常多的说话，会产生较长的查询字符串
要缩短查询的字符串，可以预先产生一张矩阵表，包含每个要旋转列的属性：

create table formatting_temp(
orderyear int primary 
key,
y2009 int,
y2010 int,
y2011 int);

 

insert into formatting_temp select 2009,1,0,0;
insert into formatting_temp select 2010,0,1,0;
insert into formatting_temp select 2011,0,0,1;

将formatting和formatting_temp联接

select custid,
        sum(qty*y2009) "2009",
        sum(qty*y2010) "2010",
        sum(qty*y2011) "2011"
from(select custid,YEAR(orderdate) a,qty
    from formatting) o
join formatting_temp p
on o.a=p.orderyear
group by custid;

 

 

 

 

【Unpivoting】

 

Unpivoting操作是将列转换成行，是Pivoting的逆操作

create table unpivoting(
custid vahrchar(10),
y2009 int,
y2010 int,
y2011 int);

insert into unpivoting
select custid,
        ifnull(sum(case when a=2009 then qty end),0) \"2009\",
        ifnull(sum(case when a=2010 then qty end),0) \"2010\",
        ifnull(sum(case when a=2011 then qty end),0) \"2011\"
from (select custid,year(orderdate) a,qty
    from formatting) p
group by custid;

这里是把之前的formatting旋转之后的内容导入到unpivoting表中。

 

目前unpivoting表的内容



解决方案及思路：
解决这个问题需要将列旋转成为行，这里使用的技巧是对每行数据产生3个副本
每个副本产生一个需要旋转的列，这个过程可以通过如下的cross join来完成

select *
from unpivoting,(select 2009 as orderyear
                union all select 2010
                union all select 2011) a;

结果为：





接下来，只需要根据orderyear列来去的对应旋转列的值就可以了

由于最后要得到




过滤掉qty=0的情况


因此这个问题的最终解决方案为：

select custid,orderyear,qty
from(
    select custid,orderyear
        case orderyear
            when 2009 then y2009
            when 2010 then y2010
            when 2011 then y2011
        end as qty
    from unpivoting,(select 2009 as orderyear
                union all select 2010
                union all select 2011) a
    ) b
where qty <> 0;