存储过程实现交叉表

物料编码 供应商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
方案一：

create table tb(物料编码 varchar(10),供应商ID varchar(10),日期 datetime,单价 decimal(18,1))
insert into tb values('001' ,'A011' ,   '2008.3.1' , 1.3 )
insert into tb values('001' ,'A011' ,   '2008.7.9' , 1.5 )
insert into tb values('001' ,'A011' ,   '2008.12.1', 1.1 )
go

select
       distinct
       物料编码 ,
       供应商ID ,
       [2008.1] = case when '2008-01-01' <= (select max(日期) from tb where 物料编码 = t.物料编码 and 供应商ID = t.供应商ID)
                       then (select top 1 单价 from tb where 物料编码 = t.物料编码 and 供应商ID = t.供应商ID and '2008-01-01' <= t.日期 order by 日期)
                       else (select top 1 单价 from tb where 物料编码 = t.物料编码 and 供应商ID = t.供应商ID and '2008-01-01' > t.日期 order by 日期 desc)
                  end ,
       [2008.2] = case when '2008-02-01' <= (select max(日期) from tb where 物料编码 = t.物料编码 and 供应商ID = t.供应商ID)
                       then (select top 1 单价 from tb where 物料编码 = t.物料编码 and 供应商ID = t.供应商ID and '2008-01-02' <= t.日期 order by 日期)
                       else (select top 1 单价 from tb where 物料编码 = t.物料编码 and 供应商ID = t.供应商ID and '2008-01-02' > t.日期 order by 日期 desc)
                  end ,
       [2009.1] = case when '2009-01-01' <= (select max(日期) from tb where 物料编码 = t.物料编码 and 供应商ID = t.供应商ID)
                       then (select top 1 单价 from tb where 物料编码 = t.物料编码 and 供应商ID = t.供应商ID and '2009-01-01' <= t.日期 order by 日期)
                       else (select top 1 单价 from tb where 物料编码 = t.物料编码 and 供应商ID = t.供应商ID and '2009-01-01' > t.日期 order by 日期 desc)
                  end
from tb t

drop table tb
方案二(首选)

13列我看没必要用到动态的:
declare @ table(物料编码 varchar(10),供应商ID varchar(10), 日期 varchar(10), 单价 dec(18,2))
insert @ select '001', 'A011',    '2008.3.1', 1.3
insert @ select '001', 'A011',   '2008.7.9', 1.5
insert @ select '001', 'A011',   '2008.12.1', 1.1
--select * from @
select
    物料编码,
    供应商ID,
    [2008.1]=sum(case when parsename(日期,3)=2008 and parsename(日期,2)=1 then 单价 else 0 end),
    [2008.2]=sum(case when parsename(日期,3)=2008 and parsename(日期,2)=2 then 单价 else 0 end),
    [2008.3]=sum(case when parsename(日期,3)=2008 and parsename(日期,2)=3 then 单价 else 0 end),
    [2008.4]=sum(case when parsename(日期,3)=2008 and parsename(日期,2)=4 then 单价 else 0 end),
    [2008.5]=sum(case when parsename(日期,3)=2008 and parsename(日期,2)=5 then 单价 else 0 end),
    [2008.6]=sum(case when parsename(日期,3)=2008 and parsename(日期,2)=6 then 单价 else 0 end),
    [2008.7]=sum(case when parsename(日期,3)=2008 and parsename(日期,2)=7 then 单价 else 0 end),
    [2008.8]=sum(case when parsename(日期,3)=2008 and parsename(日期,2)=8 then 单价 else 0 end),
    [2008.9]=sum(case when parsename(日期,3)=2008 and parsename(日期,2)=9 then 单价 else 0 end),
    [2008.10]=sum(case when parsename(日期,3)=2008 and parsename(日期,2)=10 then 单价 else 0 end),
    [2008.11]=sum(case when parsename(日期,3)=2008 and parsename(日期,2)=11 then 单价 else 0 end),
    [2008.12]=sum(case when parsename(日期,3)=2008 and parsename(日期,2)=12 then 单价 else 0 end),
    [2009.1]=sum(case when parsename(日期,3)=2009 and parsename(日期,2)=1 then 单价 else 0 end)
from @
group by 物料编码,供应商ID

方案三

use Tempdb
go
--> -->
set  nocount on ;
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([物料编码] nvarchar(3),[供应商ID] nvarchar(4),[日期] Datetime,[单价] decimal(18,2))
Insert #T
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
declare @minDate datetime,@maxDate datetime
Select @minDate=min(convert(varchar(7),[日期],102))+'.01',@maxDate=max(convert(varchar(7),[日期],102))+'.01' from #T
--
if not object_id('Tempdb..#') is null
    drop table #
select top 100 ID=Identity(int,0,1) into # from syscolumns a,syscolumns b

if not object_id('Tempdb..#Date') is null
    drop table #Date
select
     t3.[物料编码],t3.[供应商ID],dateadd(m,t2.ID,t.Date) as Date,[单价]=cast(0 as decimal(18,2))
into #Date
from (select @minDate as Date)t
cross join
    # t2
cross join
    (select distinct [物料编码],[供应商ID] from #T)t3
where dateadd(m,t2.ID,t.Date)<=@maxDate

update d
set [单价]=(select top 1 [单价] from #T where convert(varchar(7),[日期],102)<=convert(varchar(7),d.Date,102) order by [日期] desc)
from #Date d

declare @s nvarchar(4000)
set @s=N' select [物料编码],[供应商ID] '
select @s=@s+','+quotename(convert(varchar(7),Date,102))+N'= max(case when convert(varchar(7),Date,102)='+quotename(convert(varchar(7),Date,102),'''')+N' then [单价] else 0 end)'
from #Date group by convert(varchar(7),Date,102)
exec(@s+N' from #Date group by [物料编码],[供应商ID]')