27,579
社区成员
发帖
与我相关
我的任务
分享
select
a.姓名,
sum(case when b.名称='交通费' then b.金额 else 0 end) as [交通费],
sum(case when b.名称='生活补贴' then b.金额 else 0 end) as [生活补贴],
sum(case when b.名称='通讯费' then b.金额 else 0 end) as [通讯费],
sum(case when b.名称='住房补贴' then b.金额 else 0 end) as [住房补贴],
sum(case when b.名称='奖金' then b.金额 else 0 end) as [奖金],
sum(case when b.名称='提成费' then b.金额 else 0 end) as [提成费],
sum(b.金额) as 合计
from
tb_info a
join
tb_sfxm b
on
a.收费项目编码=b.项目编码
group by
a.姓名
if object_id('[tb_sfxm]') is not null drop table [tb_sfxm]
go
create table [tb_sfxm]([项目编码] varchar(3),[名称] varchar(8),[金额] int)
insert [tb_sfxm]
select '001','交通费',500 union all
select '002','生活补贴',500 union all
select '003','通讯费',500 union all
select '004','住房补贴',500 union all
select '005','奖金',500 union all
select '006','提成费',1000
go
if object_id('[tb_info]') is not null drop table [tb_info]
go
create table [tb_info]([收费项目编码] varchar(3),[姓名] varchar(4))
insert [tb_info]
select '001','张三' union all
select '002','张三' union all
select '003','张三' union all
select '004','张三' union all
select '005','张三' union all
select '001','李四' union all
select '002','李四' union all
select '003','李四' union all
select '004','李四' union all
select '001','龙五' union all
select '002','龙五' union all
select '003','龙五'
go
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')
+'sum(case when b.名称='''+名称+''' then b.金额 else 0 end) as ['+名称+']'
from
(select distinct 名称 from tb_sfxm) t
exec ('select a.姓名,'
+@sql
+',sum(b.金额) as 合计 from tb_info a join tb_sfxm b on a.收费项目编码=b.项目编码 group by a.姓名'
)
/**
姓名 奖金 交通费 生活补贴 提成费 通讯费 住房补贴 合计
---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
李四 0 500 500 0 500 500 2000
龙五 0 500 500 0 500 0 1500
张三 500 500 500 0 500 500 2500
(3 行受影响)
**/