declare @sql varchar(8000)
set @sql = 'select 卡号,sum(金额) 总金额,sum(次数) 总次数'
select @sql = @sql + ',sum(case 日期 when '''+cast(日期 as varchar(10))+''' then 金额 else 0 end) ['+cast(日期 as varchar(10))+']'
from (select distinct 日期 from 有一表) as a
select @sql = @sql+' from 有一表 group by 卡号'exec(@sql)
go
set @sql = 'select 卡号,sum(金额) 总金额,sum(次数) 总次数'
select @sql = @sql + ',sum(case 日期 when '''+cast(日期 as varchar(10))+''' then 金额 else 0 end) ['+cast(日期 as varchar(10))+']'
from (select distinct 日期 from 有一表) as a
select @sql = @sql+' from 有一表 group by 卡号'exec(@sql)
go
sum(case when 日期='0301' then 金额 else 0 end) as 0301金额,
sum(case when 日期='0302' then 金额 else 0 end) as 0302金额,
sum(case when 日期='0303' then 金额 else 0 end) as 0303金额,
......
sum(case when 日期='0306' then 金额 else 0 end) as 0306金额
from 表 group by 卡号
,sum(次数) as [总次数(0301-0306)]
,sum(cast when 日期='0301' then 金额 end) as [0301金额]
,sum(cast when 日期='0302' then 金额 end) as [0302金额]
,sum(cast when 日期='0303' then 金额 end) as [0303金额]
,sum(cast when 日期='0304' then 金额 end) as [0304金额]
,sum(cast when 日期='0305' then 金额 end) as [0305金额]
,sum(cast when 日期='0306' then 金额 end) as [0306金额]
from 你的表 group by 卡号