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;