1:
Select 存货编号,sum(case when 雇员号 = '01' then 销售数量 else 0 end) as 号01,
sum(case when 雇员号 = '02' then 销售数量 else 0 end) as 号02
from testdata group by 存货编号
2:
Select 存货编号,sum(case when 雇员号 = '01' then 销售数量 else 0 end) as 号01,
sum(case when 雇员号 = '02' then 销售数量 else 0 end) as 号02,
sum(销售数量) as 合计
from testdata group by 存货编号
Select 存货编号,sum(case when 雇员号 = '01' then 销售数量 else 0 end) as 号01,
sum(case when 雇员号 = '02' then 销售数量 else 0 end) as 号02
from testdata group by 存货编号
2:
Select 存货编号,sum(case when 雇员号 = '01' then 销售数量 else 0 end) as 号01,
sum(case when 雇员号 = '02' then 销售数量 else 0 end) as 号02,
sum(销售数量) as 合计
from testdata group by 存货编号
set @sql = 'select 存货编号'
select @sql = @sql + ',sum(case 雇员号 when '''+cast(雇员号 as varchar(10))+''' then 销售数量 else 0 end) ['+cast(雇员号 as varchar(10))+']'
from (select distinct 雇员号 from testdata) as a
select @sql = @sql+',sum(销售数量) 合计 from testdata group by 存货编号'exec(@sql)
go
declare @sql varchar(8000)
set @sql = 'select 存货编号'
select @sql = @sql + ',sum(case 雇员号 when '''+cast(雇员号 as varchar(10))+''' then 销售数量 else 0 end) ['+cast(雇员号 as varchar(10))+']'
from (select distinct 雇员号 from testdata) as a
select @sql = @sql+',sum(销售数量) 合计 from testdata group by 存货编号'exec(@sql)
go