疑难的汇总问题----(后续问题)

[hq0927]

create table tb(单据编号 varchar(10), 商品编号 varchar(10), 架位号 varchar(10), 进货价 decimal(18,1), 日期 datetime)
insert into tb values('J0001' , '12081' , 'A' , 16 , '2008-10-01') 
insert into tb values('J0099' , '12081' , 'A' , 15 , '2008-12-01') 
insert into tb values('J0009' , '12155' , 'B' , 8 , '2008-09-28') 
insert into tb values('J0001' , '11012' , 'A' , 7 , '2008-10-01') 
insert into tb values('J0008' , '12081' , 'B' , 15.5 , '2008-11-05') 
insert into tb values('J0009' , '11012' , 'C' , 9 , '2008-11-08') 
insert into tb values('J0015' , '12081' , 'C' , 16 , '2008-11-07')
go

select 商品编号,
       A = (select 进货价 from tb o where 日期 = (select max(日期) from tb n where 商品编号 = m.商品编号 and 架位号 = 'A') and o.商品编号 = m.商品编号),
       B = (select 进货价 from tb o where 日期 = (select max(日期) from tb n where 商品编号 = m.商品编号 and 架位号 = 'B') and o.商品编号 = m.商品编号),
       C = (select 进货价 from tb o where 日期 = (select max(日期) from tb n where 商品编号 = m.商品编号 and 架位号 = 'C') and o.商品编号 = m.商品编号)
from (select distinct 商品编号 from tb) m 

drop table tb 

/*

商品编号       A                    B                    C                    
---------- -------------------- -------------------- -------------------- 
11012      7.0                  NULL                 9.0
12081      15.0                 15.5                 16.0
12155      NULL                 8.0                  NULL

（所影响的行数为 3 行）
*/



如果要变成动态怎么改??


变成  A.B.C.D.E...
       A = (select 进货价 from tb o where 日期 = (select max(日期) from tb n where 商品编号 = m.商品编号 and 架位号 = 'A') and o.商品编号 = m.商品编号),
       B = (select 进货价 from tb o where 日期 = (select max(日期) from tb n where 商品编号 = m.商品编号 and 架位号 = 'B') and o.商品编号 = m.商品编号),
       C = (select 进货价 from tb o where 日期 = (select max(日期) from tb n where 商品编号 = m.商品编号 and 架位号 = 'C') and o.商品编号 = m.商品编号)
       D=.....
       E...

[dawugui]

create table tb(单据编号 varchar(10), 商品编号 varchar(10), 架位号 varchar(10), 进货价 decimal(18,1), 日期 datetime)
insert into tb values('J0001' , '12081' , 'A' , 16 , '2008-10-01') 
insert into tb values('J0099' , '12081' , 'A' , 15 , '2008-12-01') 
insert into tb values('J0009' , '12155' , 'B' , 8 , '2008-09-28') 
insert into tb values('J0001' , '11012' , 'A' , 7 , '2008-10-01') 
insert into tb values('J0008' , '12081' , 'B' , 15.5 , '2008-11-05') 
insert into tb values('J0009' , '11012' , 'C' , 9 , '2008-11-08') 
insert into tb values('J0015' , '12081' , 'C' , 16 , '2008-11-07')
go

declare @sql varchar(8000)
set @sql = 'select 商品编号 '
select @sql = @sql + ' , max(case 架位号 when ''' + 架位号 + ''' then 进货价 end) [' + 架位号 + ']'
from (select distinct 架位号 from tb) as a
set @sql = @sql + ' from (select * from tb t where 日期 = (select max(日期) from tb where 商品编号 = t.商品编号 and 架位号 = t.架位号)) m group by 商品编号'
exec(@sql) 

drop table tb

/*
商品编号       A                    B                    C                    
---------- -------------------- -------------------- -------------------- 
11012      7.0                  NULL                 9.0
12081      15.0                 15.5                 16.0
12155      NULL                 8.0                  NULL
*/

[csdyyr]

--用动态
create table tb(单据编号 varchar(10), 商品编号 varchar(10), 架位号 varchar(10), 进货价 decimal(18,1), 日期 datetime)
insert into tb values('J0001' , '12081' , 'A' , 16 , '2008-10-01') 
insert into tb values('J0099' , '12081' , 'A' , 15 , '2008-12-01') 
insert into tb values('J0009' , '12155' , 'B' , 8 , '2008-09-28') 
insert into tb values('J0001' , '11012' , 'A' , 7 , '2008-10-01') 
insert into tb values('J0008' , '12081' , 'B' , 15.5 , '2008-11-05') 
insert into tb values('J0009' , '11012' , 'C' , 9 , '2008-11-08') 
insert into tb values('J0015' , '12081' , 'D' , 16 , '2008-11-07')
go

DECLARE @SQL NVARCHAR(4000)
SET @SQL=N''
SELECT @SQL=@SQL+N','+N'MAX(CASE WHEN 架位号='''+架位号+N''' THEN 进货价 END) AS '''+架位号+N''''
FROM TB
GROUP BY 架位号

SET @SQL=N'SELECT 商品编号'+@SQL+N' FROM TB GROUP BY 商品编号'

EXEC(@SQL)

DROP TABLE TB
/*
商品编号       A                    B                    C                    D                    
---------- -------------------- -------------------- -------------------- -------------------- 
11012      7.0                  NULL                 9.0                  NULL
12081      16.0                 15.5                 NULL                 16.0
12155      NULL                 8.0                  NULL                 NULL
*/

[dawugui]

--这是我增加了几条D,E,F的数据做的测试.

create table tb(单据编号 varchar(10), 商品编号 varchar(10), 架位号 varchar(10), 进货价 decimal(18,1), 日期 datetime)
insert into tb values('J0001' , '12081' , 'A' , 16 , '2008-10-01') 
insert into tb values('J0099' , '12081' , 'A' , 15 , '2008-12-01') 
insert into tb values('J0009' , '12155' , 'B' , 8 , '2008-09-28') 
insert into tb values('J0001' , '11012' , 'A' , 7 , '2008-10-01') 
insert into tb values('J0008' , '12081' , 'B' , 15.5 , '2008-11-05') 
insert into tb values('J0009' , '11012' , 'C' , 9 , '2008-11-08') 
insert into tb values('J0015' , '12081' , 'C' , 16 , '2008-11-07')
insert into tb values('J0008' , '12081' , 'D' , 15.5 , '2008-11-05') 
insert into tb values('J0009' , '11012' , 'F' , 9 , '2008-11-08') 
insert into tb values('J0015' , '12081' , 'E' , 16 , '2008-11-07')
go

declare @sql varchar(8000)
set @sql = 'select 商品编号 '
select @sql = @sql + ' , max(case 架位号 when ''' + 架位号 + ''' then 进货价 end) [' + 架位号 + ']'
from (select distinct 架位号 from tb) as a
set @sql = @sql + ' from (select * from tb t where 日期 = (select max(日期) from tb where 商品编号 = t.商品编号 and 架位号 = t.架位号)) m group by 商品编号'
exec(@sql) 

drop table tb

/*
商品编号       A                    B                    C                    D                    E                    F                    
---------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 
11012      7.0                  NULL                 9.0                  NULL                 NULL                 9.0
12081      15.0                 15.5                 16.0                 15.5                 16.0                 NULL
12155      NULL                 8.0                  NULL                 NULL                 NULL                 NULL
*/