现有一表数据如下
设备 加油量 加油日期
A1 10 2012-10-1
A2 2 2012-10-2
A3 3 2012-10-1
现在想得到10月份每天每台设备的加油汇总表，如下
日期
设备 1 2 3 4 5 。。。。 31 1-31
A1 10 10
A2 2 2
A3 3 3
A4 0
日统计 13 2 15
------解决方案--------------------
在精华贴中搜索行列转换
------解决方案--------------------
- SQL code
if OBJECT_ID('Business_TB','U') is not null drop table Business_TBgo--虚拟出一张业务表with cte as( select number as 设备, number+10 as 加油量, DATEADD(DD,number,'2011-10-01') as 加油日期 from master..spt_values where type='p' and number between 0 and 30)select * into Business_TB from ctegoselect * from Business_TB/*设备 加油量 加油日期----------- ----------- -----------------------0 10 2011-10-01 00:00:00.0001 11 2011-10-02 00:00:00.0002 12 2011-10-03 00:00:00.0003 13 2011-10-04 00:00:00.0004 14 2011-10-05 00:00:00.0005 15 2011-10-06 00:00:00.0006 16 2011-10-07 00:00:00.0007 17 2011-10-08 00:00:00.0008 18 2011-10-09 00:00:00.0009 19 2011-10-10 00:00:00.00010 20 2011-10-11 00:00:00.00011 21 2011-10-12 00:00:00.00012 22 2011-10-13 00:00:00.00013 23 2011-10-14 00:00:00.00014 24 2011-10-15 00:00:00.00015 25 2011-10-16 00:00:00.00016 26 2011-10-17 00:00:00.00017 27 2011-10-18 00:00:00.00018 28 2011-10-19 00:00:00.00019 29 2011-10-20 00:00:00.00020 30 2011-10-21 00:00:00.00021 31 2011-10-22 00:00:00.00022 32 2011-10-23 00:00:00.00023 33 2011-10-24 00:00:00.00024 34 2011-10-25 00:00:00.00025 35 2011-10-26 00:00:00.00026 36 2011-10-27 00:00:00.00027 37 2011-10-28 00:00:00.00028 38 2011-10-29 00:00:00.00029 39 2011-10-30 00:00:00.00030 40 2011-10-31 00:00:00.000(31 row(s) affected)*/