问题一:
select sum(Amount) 总金额,
sum(case when Pay1='现金' then am1 else 0 end)+sum(case when Pay2='现金' then am2 else 0 end) 现金,
sum(case when Pay1='支票' then am1 else 0 end)+sum(case when Pay2='支票' then am2 else 0 end) 支票,
sum(case when Pay1='信用卡' then am1 else 0 end)+sum(case when Pay2='信用卡' then am2 else 0 end) 信用卡,
sum(case when Pay1='购物卷' then am1 else 0 end)+sum(case when Pay2='购物卷' then am2 else 0 end) 购物卷
from t2
select sum(Amount) 总金额,
sum(case when Pay1='现金' then am1 else 0 end)+sum(case when Pay2='现金' then am2 else 0 end) 现金,
sum(case when Pay1='支票' then am1 else 0 end)+sum(case when Pay2='支票' then am2 else 0 end) 支票,
sum(case when Pay1='信用卡' then am1 else 0 end)+sum(case when Pay2='信用卡' then am2 else 0 end) 信用卡,
sum(case when Pay1='购物卷' then am1 else 0 end)+sum(case when Pay2='购物卷' then am2 else 0 end) 购物卷
from t2
set @sql =''
select @sql=@sql + ',sum(case when Object ='''+Object +''' then grade
else 0 end ) as '+ grade
from (select Object from A1) aaa
exec ('select ID, name ' + @sql + ' from a2 group by ID , name ')
这个看看吧
select id,name,
sum(case when Object='数学' then grade else 0 end) 数学,
sum(case when Object='英语' then grade else 0 end) 英语,
sum(case when Object='语文' then grade else 0 end) 语文
from a2
group by id,name
set @sql =''
select @sql=@sql+',sum(case when Object ='''+Object +''' then grade end ) ['+Object+']'from A1
exec ('select ID, name ' + @sql + ' from a2 group by ID , name ')
set @sql =''
select @sql=@sql+',sum(case when Object ='''+Object +''' then grade end ) ['+Object+']'from A1
exec ('select ID,name ' + @sql + ' from a2 group by ID,name')
declare varchar(8000)
select * into #
from (select Pay1 PayType ,am1 am from t2 union all select Pay2 PayType ,am2 am from t2 )
set @sql =''
set @sql =@sql + ',sum(case when PayType ='''+PayType+''' then isnull(am,0)
else 0 end ) as '+ PayType
from (select PayType from t1) aaa
exec (select sum(am) 总金额 '+ @sql + ' from #')
问题1:
declare varchar(8000)
select * into #
from (select Pay1 PayType ,am1 am from t2 union all select Pay2 PayType ,am2 am from t2 )aaa
set @sql =''
select @sql =@sql + ',sum(case when PayType ='''+PayType+''' then isnull(am,0)
else 0 end ) as ['+ PayType+']' from t1
exec (select sum(am) 总金额 '+ @sql + ' from #')
问题2:
参考"大力"
感谢大力、monkey、扬帆破浪和游泳的渔。