表A
其中RouteCode，DriveDate是主键
RouteCode RouteName DriveDate 11 12 13 14 15
3 花 2007-10-05 00:00:00 Null Null Null null null
4 草 2007-10-05 00:00:00 Null Null Null null null
5 树 2007-10-05 00:00:00 Null Null Null null null
3 木 2007-10-06 00:00:00 Null Null Null null null
4 石头 2007-10-06 00:00:00 Null Null Null null null
表B
其中RoutCode，DriveDate主键 并且B表还要不断的加数据，ShapeCode有可能加入14的，15的
RouteCode DriveDate ShapeCode Amount
3 2007-10-05 00:00:00 11 65
4 2007-10-05 00:00:00 11 5
5 2007-10-05 00:00:00 11 15
3 2007-10-05 00:00:00 12 65
4 2007-10-05 00:00:00 12 7
5 2007-10-05 00:00:00 12 13
3 2007-10-05 00:00:00 13 10
4 2007-10-05 00:00:00 13 5
5 2007-10-05 00:00:00 13 15
3 2007-10-06 00:00:00 11 10
4 2007-10-06 00:00:00 11 11
把上面相同RouteCode，DriveDate的Amount差入A表中
变成如下所示
RouteCode RouteName DriveDate 11 12 13 14 15
3 花 2007-10-05 65 65 10 null null
4 草 2007-10-05 5 7 5 null null
5 树 2007-10-05 15 13 15 null null
3 木 2007-10-06 10 Null Null null null
4 石头 2007-10-06 11 Null Null null null
我的不清楚RouteCode，RouteName，DriveDate怎么对应。在线等待，上午有些不明白。
还有怎么合并相同日期和RouteCode的同时进行行列转换和Amount数据的填充，一直在线
------解决方案--------------------
select a.RouteCode,a.RouteName,a.DriveDate,t.[11],t.[12],t.[13],t.[14],t.[15] from a,
(
select RouteCode,DriveDate,
sum(case shapecode when 11 then amount else 0 end) [11],
sum(case shapecode when 12 then amount else 0 end) [12],
sum(case shapecode when 13 then amount else 0 end) [13],
sum(case shapecode when 14 then amount else 0 end) [14],
sum(case shapecode when 15 then amount else 0 end) [15]
from B
group by RouteCode,DriveDate
) t
where a.RouteCode = t.RouteCode and a.DriveDate = t.DriveDate
------解决方案--------------------
--完善下老乌龟的代码：
--update
update a
set a.[11] = t.[11],
set a.[12] = t.[12],
set a.[13] = t.[13],
set a.[14] = t.[14],
set a.[15] = t.[15]
from a,(
select RouteCode,DriveDate,
sum(case shapecode when 11 then amount else 0 end) [11],
sum(case shapecode when 12 then amount else 0 end) [12],
sum(case shapecode when 13 then amount else 0 end) [13],
sum(case shapecode when 14 then amount else 0 end) [14],
sum(case shapecode when 15 then amount else 0 end) [15]
from B
group by RouteCode,DriveDate
) t
where a.RouteCode = t.RouteCode and a.DriveDate = t.DriveDate
----insert
insert into a(RounteCode,DriveDate,[11],[12],[13],[14],[15])