create table tb(workshop_id int,resour_name varchar(20),active_name varchar(20),distrb_cost int)
insert into tb select 1,'工人工资','培训',400
union all select 2,'管理费用','工作计划',300
union all select 1,'管理费用','工作计划',200
union all select 2,'工人工资','培训',10
declare @sql varchar(8000)
set @sql='select workshop_id,resour_name'
select @sql=@sql+',['+active_name+']=sum(case active_name when '''+active_name+''' then distrb_cost else 0 end)' from tb group by active_name
exec(@sql+' from tb group by workshop_id,resour_name order by workshop_id,resour_name')
--動態語句
Declare @S Nvarchar(4000)
Set @S=''
Select @S=@S+',SUM(Case active_name When N'''+active_name+''' Then distrb_cost Else 0 End) As'+active_name From (Select Distinct active_name From A) B
Select @S='Select workshop_id,resour_name '+@S+' From A Group By workshop_id,resour_name Order By workshop_id,resour_name'
EXEC(@S)
Select
workshop_id,
resour_name,
SUM(Case active_name When '培训' Then distrb_cost Else 0 End) As 培训,
SUM(Case active_name When '工作计划' Then distrb_cost Else 0 End) As 工作计划
From A
Group By workshop_id,resour_name
Order By workshop_id,resour_name