if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([类别] varchar(6),[金额] int)
insert [tb]
select '西药费',100 union all
select '草药费',200 union all
select '材料费',300
go

declare @sql varchar(8000)
select
  @sql=isnull(@sql+',','')
  +'sum(case when 类别='''+类别+''' then 金额 else 0 end) as ['+类别+']'
from
(select distinct 类别 from tb) t
exec ('select '+@sql+' from tb')

/**
材料费         草药费         西药费
----------- ----------- -----------
300         200         100

(1 行受影响)
**/

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

SQL code

declare @sql nvarchar(200)
declare @list nvarchar(100)
set @list=(select stuff((select ','+[类别] from tb for xml path('')),1,1,''))
set @sql = N'
select ' + @list + N'
from tb pivot(sum([金额]) for [类别] in ('+ @list + N')) b'

print @sql

exec(@sql)