create table tb(入库单号 varchar(20),订单号 varchar(20),产品编号 varchar(20),入库数量 dec(10,1),入库日期 varchar(10)) insert into tb select 'G08-005166','20060320011','0210710001',346.0,'2006-03-30' union all select 'G08-005315','20060320011','0210710001',54.0,'2006-03-31' union all select 'G08-005374','20060320031','0210130001',600.0,'2006-03-31' union all select 'G08-005592','20060320031','0210130001',400.0,'2006-04-02' select a.*,[批次]=(select count(*)+1 from tb where 产品编号=a.产品编号 and 订单号=a.订单号 and 入库日期<a.入库日期) into ## from tb a order by 入库日期 declare @sql varchar(8000) set @sql='select 订单号,产品编号' select @sql=@sql+',[入库数量'+cast(批次 as varchar)+']=sum(case 批次 when '+cast(批次 as varchar)+' then 入库数量 else 0 end),[入库日期'+cast(批次 as varchar)+']=max(case 批次 when '+cast(批次 as varchar)+' then 入库日期 else '''' end)' from ## group by 批次 exec(@sql+' from ## group by 订单号,产品编号 order by 订单号')drop table tb,## --这样?
insert into tb select 'G08-005166','20060320011','0210710001',346.0,'2006-03-30'
union all select 'G08-005315','20060320011','0210710001',54.0,'2006-03-31'
union all select 'G08-005374','20060320031','0210130001',600.0,'2006-03-31'
union all select 'G08-005592','20060320031','0210130001',400.0,'2006-04-02' select a.*,[批次]=(select count(*)+1 from tb where 产品编号=a.产品编号 and 订单号=a.订单号 and 入库日期<a.入库日期) into ## from tb a order by 入库日期
declare @sql varchar(8000)
set @sql='select 订单号,产品编号'
select @sql=@sql+',[入库数量'+cast(批次 as varchar)+']=sum(case 批次 when '+cast(批次 as varchar)+' then 入库数量 else 0 end),[入库日期'+cast(批次 as varchar)+']=max(case 批次 when '+cast(批次 as varchar)+' then 入库日期 else '''' end)'
from ## group by 批次
exec(@sql+' from ## group by 订单号,产品编号 order by 订单号')drop table tb,##
--这样?