declare @t table(pluid int,qty int,shpid varchar(10))
insert into @t select 123 ,20 ,'01'
union all select 123 ,10 ,'02'
union all select 123 ,15 ,'03'
union all select 124 ,12 ,'01'
union all select 124 ,14 ,'02'select pluid,
[01]=sum(case shpid when '01' then qty else 0 end),
[02]=sum(case shpid when '02' then qty else 0 end),
[03]=sum(case shpid when '03' then qty else 0 end)
from @t
group by pluid
insert into @t select 123 ,20 ,'01'
union all select 123 ,10 ,'02'
union all select 123 ,15 ,'03'
union all select 124 ,12 ,'01'
union all select 124 ,14 ,'02'select pluid,
[01]=sum(case shpid when '01' then qty else 0 end),
[02]=sum(case shpid when '02' then qty else 0 end),
[03]=sum(case shpid when '03' then qty else 0 end)
from @t
group by pluid
insert into tb select 123 ,20 ,'01'
union all select 123 ,10 ,'02'
union all select 123 ,15 ,'03'
union all select 124 ,12 ,'01'
union all select 124 ,14 ,'02'declare @sql varchar(8000)
set @sql='select pluid'
select @sql=@sql+',['+shpid+']=sum(case shpid when '''+shpid+''' then qty else 0 end)' from tb group by shpid
exec(@sql+' from tb group by pluid')drop table tb
if exists(select * from sysobjects where name='Tb' and xtype='U') drop table Tb
GO
--生成测试用数据,为SELECT初始环境
CREATE TABLE Tb(pluid int,qty int,shpid varchar(10))
INSERT INTO Tb SELECT 123,20,'01'
union all select 123,10,'02'
union all select 123,15,'03'
union all select 124,12 ,'01'
union all select 124,14,'02'select * from Tb
GO
--此处为用来查询的SELECT语句
Declare @Sql Varchar(8000)
select @sql='select pluid'
Select @sql=@sql+','''+shpid+'''=max(case shpid when '''+shpid+''' then qty else 0 end)' from Tb group by shpid order by shpid
exec (@sql+ ' from Tb group by pluid')
GO--删除测试散数据
DROP TABLE Tb
pluid 01 02 03
----------- ----------- ----------- -----------
123 20 10 15
124 12 14 0*/ -- 完成 --