使用pivot进行报表的行转列

        行转列在报表中是用的很多的，以前在sql中使用横转列的时候总是使用selet case来处理，即便sql2005现在都要被sql2008代替了，偶还一次没有用过他提供的新东东pivot。一时兴起，还是学了学。

        有两个简化的表(供应商表和供货记录表)
create table supplier (sid int,suppliershortname varchar(20))--记录供应商的简称和代码
create table warehouserecord (sid int,materialid int,qty numeric(18,2),price numeric(18,4),billdate smalldatetime)--记录每天供应商的送货记录

 

        管理需求上下面这个是经常遇到的，需要知道最近一个月或一周内每个供应商的供货情况，可能需要得到的表样式是下面的

日期         供应商A供货金额  供应商B供货金额  供应商Ｃ供货金额 .....
2008-11-25         300.2           250.0            2562.65

 

我们创建好表后填入记录，开始下面的工作
--写入供应商记录
insert into supplier (sid,suppliershortname) select 1,'供应商A'
insert into supplier (sid,suppliershortname) select 2,'供应商B'
insert into supplier (sid,suppliershortname) select 3,'供应商C'
--送货记录
insert into warehouserecord (sid,materialid,qty,price,billdate) select 1,10000,200,5.4,'2008-11-25'
insert into warehouserecord (sid,materialid,qty,price,billdate) select 2,11000,30,95.4,'2008-11-25'
insert into warehouserecord (sid,materialid,qty,price,billdate) select 1,15000,50,6.32,'2008-11-25'
insert into warehouserecord (sid,materialid,qty,price,billdate) select 3,11000,220,9.2,'2008-11-25'

 

要达到我们需要的效果，如果还是使用sql 2000中的方式我们也很容易
select c.billdate,
sum(case when c.suppliershortname='供应商A' then c.qty*c.price else 0 end) as 供应商A,
sum(case when c.suppliershortname='供应商b' then c.qty*c.price else 0 end) as 供应商B,
sum(case when c.suppliershortname='供应商C' then c.qty*c.price else 0 end) as 供应商c
from (select a.suppliershortname,b.* from supplier a,warehouserecord b where a.sid=b.sid) c group by c.billdate
这样就可以得到我们的结果了，如果供应商比较多，是不是有点长了。

写完了sql2000的，现在如果换成了sql2005的pivot,则是如何写的呢？

select billdate,[供应商A],[供应商B],[供应商C] from
(select a.suppliershortname,sum(b.qty*b.price) as daymoney,b.billdate from supplier a,warehouserecord b where a.sid=b.sid group by b.billdate,a.suppliershortname) c
pivot (sum(daymoney) for suppliershortname in ([供应商A],[供应商B],[供应商C])) AS unpvt
看起来好像只是语句短了点，其他的差别不大吧。

 

上面两种方法写的都是固定字段的，如果供应商很多，或者要监控的供应商是一直变化的，按上面那样方法写还不死人啊。
对于不固定列的，sql2000是如何写的呢？
declare @sql varchar(8000)
set @sql = 'select billdate '
select @sql = @sql + ' , sum(case c.suppliershortname when ''' + suppliershortname + ''' then c.price*c.qty else 0 end) [' + suppliershortname + ']'
from (select distinct suppliershortname from supplier) as a
set @sql = @sql + ' from (select a.suppliershortname,b.* from supplier a,warehouserecord b where a.sid=b.sid) c  group by c.billdate'
exec(@sql)
执行的结果就和上面的一样了。

 

那么使用sql2005提供的新方法pivot又是如何写的呢？

DECLARE @selstr varchar(1000)
declare @sql varchar(8000)
SELECT @selstr = isnull(@selstr + ',','')+ '['+ltrim(suppliershortname)+']'
FROM (SELECT suppliershortname FROM supplier) d
set @sql='select billdate,'+@selstr+' from (select a.suppliershortname,b.billdate,sum(b.qty*b.price) as daymoney from supplier a,warehouserecord b where a.sid=b.sid group by b.billdate,a.suppliershortname) c '
set @sql=@sql+'pivot (sum(daymoney) for suppliershortname in ('+@selstr+')) AS unpvt'
print @sql
exec(@sql)

 

        感觉出差别来了没有？

        说真的，就自己的感觉，我还是觉得以前的方法比较好理解一点。也许人总是活着习惯中，当你习惯一种方法时，改变是真的有点困难，除非你努力将新方法成为了你新的习惯。