select b.科室名称,
sum(case when a.费用名称='手术费' then 金额 else 0 end) as 手术费,
sum(case when a.费用名称='检查费' then 金额 else 0 end) as 检查费,
sum(case when a.费用名称='药费' then 金额 else 0 end) as 药费,
sum(case when a.费用名称='B超' then 金额 else 0 end) as B超,
...
from 表1 a,表2 b
where a.科室编码=b.科室编码
group by b.科室名称
sum(case when a.费用名称='手术费' then 金额 else 0 end) as 手术费,
sum(case when a.费用名称='检查费' then 金额 else 0 end) as 检查费,
sum(case when a.费用名称='药费' then 金额 else 0 end) as 药费,
sum(case when a.费用名称='B超' then 金额 else 0 end) as B超,
...
from 表1 a,表2 b
where a.科室编码=b.科室编码
group by b.科室名称
set @sql = 'select b.科室名称,'select @sql = @sql + 'sum(case a.费用名称 when '''+费用名称+'''
then a.金额 else 0 end) as '''+费用名称+''','
from (select distinct 费用名称 from 表1) as aselect @sql = left(@sql,len(@sql)-1) + ' from 表1 a join 表2 b on a.科室编码=b.科室编码 group by b.科室名称'exec(@s
表a.费用名称不是固定的数目,也不一定非有手术费,也很有几百种费用名称。
set @sql = 'select b.科室名称,'select @sql = @sql + 'sum(case a.费用名称 when '''+费用名称+'''
then a.金额 else 0 end) as '''+费用名称+''','
from (select distinct 费用名称 from 表1) as aselect @sql = left(@sql,len(@sql)-1) + ' from 表1 a join 表2 b on a.科室编码=b.科室编码 group by b.科室名称'exec(@sql)
go
set @sql = 'select b.科室名称 ,'select @sql = @sql + 'sum(case 费用名称 when '''+费用名称 +'''
then 金额 else 0 end) as ['+费用名称 +'],'
from (select distinct 金额 from 库表) aselect @sql = left(@sql,len(@sql)-1) + ' from 表1 a join 表2 b on
a. 科室编码=b. 科室编码 group by b.科室名称' exec(@sql)
set @sql = 'select b.科室名称,'select @sql = @sql + 'sum(case a.费用名称 when '''+费用名称+'''
then a.金额 else 0 end) as '''+费用名称+''','
from (select distinct 费用名称 from 表1) as a
-------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^取得你的表有那些费用名称
/*
根据那些费用名称动态构造:
sum(case when a.费用名称='手术费' then 金额 else 0 end) as 手术费,
sum(case when a.费用名称='检查费' then 金额 else 0 end) as 检查费,
sum(case when a.费用名称='药费' then 金额 else 0 end) as 药费,
sum(case when a.费用名称='B超' then 金额 else 0 end) as B超,
...
*/select @sql = left(@sql,len(@sql)-1) + ' from 表1 a join 表2 b on a.科室编码=b.科室编码 group by b.科室名称'print @sql
--exec(@sql)
go