日期:2014-05-19  浏览次数:20737 次

向各位请教如何做这样一个交叉报表?
物料编码 供应商ID 日期 单价
001 A011 2008.3.1 1.3
001 A011 2008.7.9 1.5
001 A011 2008.12.1 1.1

以上是不同日期同一个物料在同一供应商的单价

我要求变成这样一种交叉报表
物料编码 供应商ID 2008.1 2008.2 2008.3 2008.4 2008.5 2008.6 2008.7 2008.8 2008.9 2008.10 2008.11 2008.12 2009.1
001 A011 1.3 1.3 1.3 1.3 1.3 1.3 1.5 1.5 1.5 1.5 1.5 1.1 1.1  

现请教各位大虾如何实现?多谢了!

------解决方案--------------------
up
------解决方案--------------------
如果是求當前日期月份時

SQL code
@maxDate=max(convert(varchar(7),[日期],102))+'.01'
改為
@maxDate=max(convert(varchar(7),getdate(),102))+'.01'

------解决方案--------------------
SQL code
--> (让你望见影子的墙)生成测试数据,时间:2009-03-13
 
if not object_id('tb') is null
    drop table tb
Go
Create table tb([物料编码] nvarchar(3),[供应商ID] nvarchar(4),[日期] datetime,[单价] decimal(18,1))
Insert tb
select N'001',N'A011','2008.3.1',1.3 union all
select N'001',N'A011','2008.7.9',1.5 union all
select N'001',N'A011','2008.12.1',1.1
Go
Select * from tb
drop table #
select id=identity(int ,1,1),*  into # from tb order by 物料编码,供应商id,日期 
select * from #
select  物料编码,供应商id,
        [2008-1]=case when '2008-1-1'<=(select 日期 from # where id=1)
                      then (select 单价 from # where id=1)
                      else (select top 1 单价 from # t where t.物料编码=s.物料编码 and t.供应商id=s.供应商id and 日期<='2008-1-13' order by 日期 desc)
                 end,
        [2008-2]=case when '2008-2-1'<=(select 日期 from # where id=1)
                      then (select 单价 from # where id=1)
                      else (select top 1 单价 from # t where t.物料编码=s.物料编码 and t.供应商id=s.供应商id and 日期<='2008-2-28' order by 日期 desc)
                 end,
        [2008-3]=case when '2008-3-1'<=(select 日期 from # where id=1)
                      then (select 单价 from # where id=1)
                      else (select top 1 单价 from # t where t.物料编码=s.物料编码 and t.供应商id=s.供应商id and 日期<='2008-3-31' order by 日期 desc)
                 end,
        [2008-4]=case when '2008-4-1'<(select 日期 from # where id=1)
                      then (select 单价 from # where id=1)
                      else (select top 1 单价 from # t where t.物料编码=s.物料编码 and t.供应商id=s.供应商id and 日期<='2008-4-30' order by 日期 desc)
                 end,
        [2008-5]=case when '2008-5-1'<(select 日期 from # where id=1)
                      then (select 单价 from # where id=1)
                      else (select top 1 单价 from # t where t.物料编码=s.物料编码 and t.供应商id=s.供应商id and 日期<='2008-5-31' order by 日期 desc)
                 end,
        [2008-6]=case when '2008-6-1'<(select 日期 from # where id=1)
                      then (select 单价 from # where id=1)
                      else (select top 1 单价 from # t where t.物料编码=s.物料编码 and t.供应商id=s.供应商id and 日期<='2008-6-30' order by 日期 desc)
                 end,
        [2008-7]=case when '2008-7-1'<(select 日期 from # where id=1)
                      then (select 单价 from # where id=1)
                      else (select top 1 单价 from # t where t.物料编码=s.物料编码 and t.供应商id=s.供应商id and 日期<='2008-7-31' order by 日期 desc)
                 end,
        [2008-8]=case when '2008-8-1'<(select 日期 from # where id=1)
                      then (select 单价 from # where id=1)
                      else (select top 1 单价 from # t where t.物料编码=s.物料编码 and t.供应商id=s.供应商id and 日期<='2008-8-31' order by 日期 desc)
                 end,
        [2008-9]=case when '2008-9-1'<(select 日期 from # where id=1)
                      then (select 单价 from # where id=1)
                      else (select top 1 单价 from # t where t.物料编码=s.物料编码 and t.供应商id=s.供应商id and 日期<='2008-9-30' order by 日期 desc)
                 end,
        [2008-10]=case when '2008-10-1'<(select 日期 from # where id=1)
                      then (select 单价 fro