create table 客户资料表(客户名称 varchar(20), 结算开始日期 int)insert into 客户资料表
select 'A', 1
union all select 'B', 26
create table 客户交易记录(客户名称 varchar(20), 交易日期 datetime, 金额 int)
insert into 客户交易记录
select 'A' ,'2013-1-1', 100
union all select 'A' ,'2013-1-15', 200
union all select 'A' ,'2013-2-2', 300
union all select 'B' ,'2013-1-1', 100
union all select 'B' ,'2013-1-26', 200
union all select 'B' ,'2013-1-27', 300
union all select 'B' ,'2013-2-1', 400
union all select 'B' ,'2013-2-25', 500
union all select 'B' ,'2013-2-26', 600
declare @year varchar(10);
declare @sql nvarchar(4000);set @year = '2013';
set @sql = '';
select @sql = @sql + ',sum(case when a.结算开始日期 = 1 and '+
'b.交易日期 >= '''+@year +'-' +ltrim(number) + '-1''' +
' and b.交易日期 < '''+
convert(varchar(10),dateadd(month,1,@year +'-' +ltrim(number) + '-1'),120)+''''+
' then b.金额 ' +
' when a.结算开始日期 = 26 and '+
'b.交易日期 >= '''+@year +'-' +ltrim(number) + '-26''' +
' and b.交易日期 < '''+
convert(varchar(10),dateadd(month,1,@year +'-' +ltrim(number) + '-26'),120)+''''+
' then b.金额 else 0 end) as ['+
right('0'+ltrim(number),2) +']'
from master..spt_values
where type = 'P'
and number >= 1 and number <= 12set @sql = 'select a.客户名称'+@sql +
' from 客户资料表 a
left join 客户交易记录 b
on a.客户名称 = b.客户名称
group by a.客户名称'--select @sqlexec(@sql)
/*
客户名称 01 02 03 04 05 06 07 08 09 10 11 12
A 300 300 0 0 0 0 0 0 0 0 0 0
B 1400 600 0 0 0 0 0 0 0 0 0 0
*/
select 'A', 1
union all select 'B', 26
create table 客户交易记录(客户名称 varchar(20), 交易日期 datetime, 金额 int)
insert into 客户交易记录
select 'A' ,'2013-1-1', 100
union all select 'A' ,'2013-1-15', 200
union all select 'A' ,'2013-2-2', 300
union all select 'B' ,'2013-1-1', 100
union all select 'B' ,'2013-1-26', 200
union all select 'B' ,'2013-1-27', 300
union all select 'B' ,'2013-2-1', 400
union all select 'B' ,'2013-2-25', 500
union all select 'B' ,'2013-2-26', 600
declare @year varchar(10);
declare @sql nvarchar(4000);set @year = '2013';
set @sql = '';
select @sql = @sql + ',sum(case when a.结算开始日期 = 1 and '+
'b.交易日期 >= '''+@year +'-' +ltrim(number) + '-1''' +
' and b.交易日期 < '''+
convert(varchar(10),dateadd(month,1,@year +'-' +ltrim(number) + '-1'),120)+''''+
' then b.金额 ' +
' when a.结算开始日期 = 26 and '+
'b.交易日期 >= '''+@year +'-' +ltrim(number) + '-26''' +
' and b.交易日期 < '''+
convert(varchar(10),dateadd(month,1,@year +'-' +ltrim(number) + '-26'),120)+''''+
' then b.金额 else 0 end) as ['+
right('0'+ltrim(number),2) +']'
from master..spt_values
where type = 'P'
and number >= 1 and number <= 12set @sql = 'select a.客户名称'+@sql +
' from 客户资料表 a
left join 客户交易记录 b
on a.客户名称 = b.客户名称
group by a.客户名称'--select @sqlexec(@sql)
/*
客户名称 01 02 03 04 05 06 07 08 09 10 11 12
A 300 300 0 0 0 0 0 0 0 0 0 0
B 1400 600 0 0 0 0 0 0 0 0 0 0
*/
insert into #A
select 'A', 1 union all select 'B', 26 create table #B(nam varchar(20), dat datetime, AMT int)
insert into #B
select 'A' ,'2013-1-1',100 union all
select 'A' ,'2013-1-15', 200 union all
select 'A' ,'2013-2-2', 300 union all
select 'B' ,'2013-1-1', 100 union all
select 'B' ,'2013-1-26', 200 union all
select 'B' ,'2013-1-27', 300 union all
select 'B' ,'2013-2-1', 400 union all
select 'B' ,'2013-2-25', 500 union all
select 'B' ,'2013-2-26', 600 create table #c (nam varchar(20), mon varchar(2), AMT int) insert into #c
select b.nam,convert(varchar(2),case when a.dat=1 then month(b.dat) when day(b.dat)>=a.dat then month(b.dat)+1 else month (b.dat) end),amt
from #A a,#B b where a.nam=b.nam declare @s varchar(max)select @s=isnull(@s+',','')+'['+mon+']'
from #C group by mon order by monset @s='select * from #C pivot(sum(amt) for mon in ('+@s+'))a'exec(@s)