select 1室金额=sum(case when 1室='是' then 金额 else 0 end),
2室金额=sum(case when 2室='是' then 金额 else 0 end),
3室金额=sum(case when 3室='是' then 金额 else 0 end),
合计金额=sum(金额)
from table1 group by 姓名
2室金额=sum(case when 2室='是' then 金额 else 0 end),
3室金额=sum(case when 3室='是' then 金额 else 0 end),
合计金额=sum(金额)
from table1 group by 姓名
,[1室]=sum(charindex([1室],'是')*金额)
,[2室]=sum(charindex([2室],'是')*金额)
,[3室]=sum(charindex([3室],'是')*金额)
,合计金额=sum(金额)
from Table1
group by 姓名
Insert into tb
select '李一','100.00','是',null,null
union all select '李一','10.00','是',null,null
union all select '李一','20.00',null,null,null
union all select '張三','20.00',null,'是',null
union all select '張三','30.00',null,null,null
union all select '張三','200.00',null,'是',null
union all select '王五','100.00',null,null,null
union all select '王五','100.00',null,null,'是'select * from tbselect 姓名,[1室金額]=sum(case when [1室]='是' then 金額 else 0 end),
[2室金額]=sum(case when [2室]='是' then 金額 else 0 end),
[3室金額]=sum(case when [3室]='是' then 金額 else 0 end),
合計金額=sum(金額)
from tb group by 姓名 order by 姓名--結果
姓名 1室金额 2室金额 三室金额 合计金额
---------------------------------------------
王五 .00 .00 100.00 200.00
李一 110.00 .00 .00 130.00
張三 .00 220.00 .00 250.00