create table Account(ID int identity(1,1),accountid int,支出资金 int,收入资金 int) insert Account select 10,99,null union all select 11,99,null union all select 12,null,80create table fund(accountid int,accountnum nvarchar(100)) insert fund select 10,N'中国银行' union all select 11,N'人民银行' union all select 12,N'现金' Declare @SQL nvarchar(4000),@sql2 nvarchar(1000),@sql3 nvarchar(1000) select @SQL='',@sql2='',@sql3='' select @SQL=@SQL+', case accountnum when N'+quotename(accountnum,'''')+' then 1 else 0 end ['+accountnum+'] ' from fund select @sql2=@sql2+',B.'+accountnum from fund select @sql3=@sql3+N',sum(case accountid when '+convert(varchar,accountid)+N' then isnull(支出资金,0)+isnull(收入资金,0) else 0 end)' from fund set @sql3=N'select N''总计'',null,sum(支出资金),sum(收入资金),'+stuff(@sql3,1,1,'')+' from Account' select @SQL=N' select convert(varchar(10),A.ID),A.accountid,A.支出资金,A.收入资金,'+stuff(@SQL2,1,1,'')+N' from Account A join (select *,'+stuff(@SQL,1,1,'')+N' from fund) B on A.accountid=B.accountid union all '+@sql3+' ' exec(@SQL)drop table Account drop table fund /* accountid 支出资金 收入资金 中国银行 人民银行 现金 ---------- ----------- ----------- ----------- ----------- ----------- ----------- 1 10 99 NULL 1 0 0 2 11 99 NULL 0 1 0 3 12 NULL 80 0 0 1 总计 NULL 198 80 99 99 80 */
insert Account
select 10,99,null
union all
select 11,99,null
union all
select 12,null,80create table fund(accountid int,accountnum nvarchar(100))
insert fund
select 10,N'中国银行'
union all
select 11,N'人民银行'
union all
select 12,N'现金' Declare @SQL nvarchar(4000),@sql2 nvarchar(1000),@sql3 nvarchar(1000)
select @SQL='',@sql2='',@sql3=''
select @SQL=@SQL+', case accountnum when N'+quotename(accountnum,'''')+' then 1 else 0 end ['+accountnum+'] ' from fund
select @sql2=@sql2+',B.'+accountnum from fund
select @sql3=@sql3+N',sum(case accountid when '+convert(varchar,accountid)+N' then isnull(支出资金,0)+isnull(收入资金,0) else 0 end)' from fund
set @sql3=N'select N''总计'',null,sum(支出资金),sum(收入资金),'+stuff(@sql3,1,1,'')+' from Account'
select @SQL=N'
select convert(varchar(10),A.ID),A.accountid,A.支出资金,A.收入资金,'+stuff(@SQL2,1,1,'')+N' from Account A
join (select *,'+stuff(@SQL,1,1,'')+N' from fund) B on A.accountid=B.accountid
union all
'+@sql3+'
'
exec(@SQL)drop table Account
drop table fund
/*
accountid 支出资金 收入资金 中国银行 人民银行 现金
---------- ----------- ----------- ----------- ----------- ----------- -----------
1 10 99 NULL 1 0 0
2 11 99 NULL 0 1 0
3 12 NULL 80 0 0 1
总计 NULL 198 80 99 99 80
*/