if object_id('material') is not null drop table material
go
create table material
(
    materialid int, 
    code varchar(1), 
    name varchar(2),
    specs varchar(2)
)
go
insert into material
select 10001 , 'A' , 'A1' , 'A2' union all
select 30005 , 'K' , 'K1' , 'K2' union all
select 30008 , 'J' , 'J1' , 'J2'
go
if object_id('view_storeqtysum') is not null drop table view_storeqtysum
go
create table view_storeqtysum
(
    storeid int,
    materialid int, 
    qty int 
)
go
insert into view_storeqtysum
select 1 , 10001 , 3000 union all
select 4 , 10001 , 400 union all
select 4 , 30005 , 900 union all
select 8 , 30008 , 30000
go
declare @sql varchar(4000)
set @sql='select a.materialid,a.code,a.name,a.specs'
select @sql=@sql+','+cast(storeid as varchar(2))+' as storeid'+cast(storeid as varchar(2))
    +', sum(case when b.storeid='+cast(storeid as varchar(2))+' then qty else 0 end) as storename'+cast(storeid as varchar(2))
from (select distinct storeid from view_storeqtysum) t
set @sql=@sql+' from material a inner join view_storeqtysum b on a.materialid=b.materialid group by a.materialid,a.code,a.name,a.specs'
--print @sql
exec(@sql)
/*
materialid  code name specs storeid1    storename1  storeid4    storename4  storeid8    storename8
----------- ---- ---- ----- ----------- ----------- ----------- ----------- ----------- -----------
10001       A    A1   A2    1           3000        4           400         8           0
30005       K    K1   K2    1           0           4           900         8           0
30008       J    J1   J2    1           0           4           0           8           30000
*/

------解决方案--------------------

SQL code

declare @sql varchar(4000)
set @sql='select a.materialid,a.code,a.name,a.specs'
select 
@sql=@sql+','+cast(storeid as varchar(2))+' as storeid'+
     cast(storeid as varchar(2))+', 
     sum(case when b.storeid='+cast(storeid as varchar(2))+' then qty else 0 end)
     as storename'+cast(storeid as varchar(2))
from (select distinct storeid from view_storeqtysum) t
set @sql=@sql+' from material a inner join view_storeqtysum b on a.materialid=b.materialid group by a.materialid,a.code,a.name,a.specs'

exec(@sql)