0

慎用Pivot

Posted on Tuesday, August 26, 2014 by 醉·醉·鱼 and labeled under ,
刚刚瞄了一眼http://www.sqlpassion.at/archive/2014/08/25/the-dangerous-beauty-of-the-pivot-operator-in-sql-server/?awt_l=KDBpM&awt_m=3eQDtEE5mzYUUTS 有点危言耸听了。其实里面就讲到一个东西，那就是pivot是如何去做grouping的。

借用http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx 的例子。先创建如下table和数据。

create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)
--drop table DailyIncome
insert into DailyIncome values ('SPIKE', 'FRI', 100)
insert into DailyIncome values ('SPIKE', 'MON', 300)
insert into DailyIncome values ('FREDS', 'SUN', 400)
insert into DailyIncome values ('SPIKE', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'TUE', 200)
insert into DailyIncome values ('JOHNS', 'WED', 900)
insert into DailyIncome values ('SPIKE', 'FRI', 100)
insert into DailyIncome values ('JOHNS', 'MON', 300)
insert into DailyIncome values ('SPIKE', 'SUN', 400)
insert into DailyIncome values ('JOHNS', 'FRI', 300)
insert into DailyIncome values ('FREDS', 'TUE', 500)
insert into DailyIncome values ('FREDS', 'TUE', 200)
insert into DailyIncome values ('SPIKE', 'MON', 900)
insert into DailyIncome values ('FREDS', 'FRI', 900)
insert into DailyIncome values ('FREDS', 'MON', 500)
insert into DailyIncome values ('JOHNS', 'SUN', 600)
insert into DailyIncome values ('SPIKE', 'FRI', 300)
insert into DailyIncome values ('SPIKE', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'FRI', 300)
insert into DailyIncome values ('JOHNS', 'THU', 800)
insert into DailyIncome values ('JOHNS', 'SAT', 800)
insert into DailyIncome values ('SPIKE', 'TUE', 100)
insert into DailyIncome values ('SPIKE', 'THU', 300)
insert into DailyIncome values ('FREDS', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'SAT', 100)
insert into DailyIncome values ('FREDS', 'SAT', 500)
insert into DailyIncome values ('FREDS', 'THU', 800)
insert into DailyIncome values ('JOHNS', 'TUE', 600)

数据应该是这样的。
VendorId   IncomeDay  IncomeAmount
---------- ---------- ------------
SPIKE      FRI        100
SPIKE      MON        300
FREDS      SUN        400
SPIKE      WED        500
SPIKE      TUE        200
JOHNS      WED        900
SPIKE      FRI        100
JOHNS      MON        300
SPIKE      SUN        400
...
SPIKE      WED        500
FREDS      THU        800
JOHNS      TUE        600

执行如下query，就可以进行一次简单的pivot操作。
select * from DailyIncome
pivot (avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay

但是，如果，这个时候DailyIncome table多增加了一个column，那情况就不会是这样的了。像http://www.sqlpassion.at/archive/2014/08/25/the-dangerous-beauty-of-the-pivot-operator-in-sql-server/?awt_l=KDBpM&awt_m=3eQDtEE5mzYUUTS 说的那样，得到的结果就不会像想象中的那样了。原因就在于pivot里面会用除了IncomeDay和IncomeAmount的column进行group操作，进而导致了pivot的结果显示的乱糟糟的。

解决方法

将DailyIncome换成Table express，即
select * from (select VendorId, IncomeDay, IncomeAmount from DailyIncome) AS t
pivot (avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay

这个问题可能会出现的比较隐蔽些，因为table的改动很难想到会影响pivot操作。

0
Responses to ... 慎用Pivot

Post a Comment