1、说明：多栏明细显示的各例，是根据用户设置显示生成，所以各列明细项目是不固定的。
2、要求：麻烦高手提供编制的方法，最好有代码复制到回贴中。谢谢！
------解决方案--------------------
一般通过组合sql生成
步骤
1、定义 多栏帐设置
2、根据设置动态生成
-------------------------- 多栏帐显示 Begin
Declare @TableRecoId nvarchar(64) ,@BillNo nvarchar(64) ,@CName nvarchar(64),@enter varchar(1)
Declare @SQlSelect varchar(4000) , @Sql nvarchar(2000) , @ColumnCount SmallInt
Set @ColumnCount = 0
Set @enter = char(13)
Create Table #tmpDetail (
Id [bigint] IDENTITY (1, 1) NOT NULL ,
Fdate [datetime] NOT NULL
)
Set @SQlSelect = 'Select ' + @enter
Set @SQlSelect = @SQlSelect + 'Fdate ' + @enter
Begin
------'---- 科目编码 -----'
DECLARE easyAccountMlt_cursor CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR
select
FinDebitCreditID = a.FinDebitCreditID ,
TableRecoId = a.TableRecoId ,
BillNo = a.BillNo ,
CName = a.CName
from 多栏帐设置 a
where a.RelaId = @多栏帐名称
order by a.RecoOrder
OPEN easyAccountMlt_cursor
FETCH NEXT FROM easyAccountMlt_cursor INTO @FinDebitCreditID , @TableRecoId , @BillNo , @CName
WHILE @@FETCH_STATUS = 0
BEGIN
set @ColumnCount = @ColumnCount + 1
-- 增加字段
set @Sql = 'Alter table #tmpDetail add Col' + convert(varchar,@ColumnCount) + ' decimal(28,10) null Default 0'
Exec ( @sql )
-- 增加对应编码
set @Sql = 'Alter table #tmpDetail add BillNo' + convert(varchar,@ColumnCount) + ' nvarchar(64) null '
Exec ( @sql )
set @Sql = 'Update #tmpDetail set BillNo' + convert(varchar,@ColumnCount) + ' = ''' [email protected] + ''''
Exec ( @sql )
Set @SQlSelect = @SQlSelect + ', Col' + convert(varchar,@ColumnCount) +' ' + @enter
-- 增加字段-------显示内容 -------
set @Sql = 'Alter table #tmpDetail add cName' + convert(varchar,@ColumnCount) + ' nvarchar(64) null '
Exec ( @sql )
set @Sql = 'Update #tmpDetail set cName' + convert(varchar,@ColumnCount) + ' = ''' [email protected] + ''''
Exec ( @sql )
Set @SQlSelect = @SQlSelect + ', cName' + convert(varchar,@ColumnCount) +' ' + @enter
FETCH NEXT FROM easyAccountMlt_cursor INTO @FinDebitCreditID , @TableRecoId , @BillNo , @CName
END
CLOSE easyAccountMlt_cursor
DEALLOCATE easyAccountMlt_cursor
------------- 处理对应金额
While @ColumnCount > 0
Begin
set @Sql = 'Update #tmpDetail Set Col' + convert(varchar,@ColumnCount) + ' = '
Set @Sql = @Sql + '(Select SUM( Case When bb.FinDebitCreditID = '''+'D' +''' then aa.CashDebit - aa.CashCredit else - aa.CashDebit + aa.CashCredit End ) ' + @enter
Set @Sql = @Sql + ' From 凭证明细表 aa inner join 科目 bb on aa.FinAccountId = bb.RecoId ' + @enter
Set @Sql = @Sql + ' Where aa.RelaId = a.FinVoucherMasterId' + @enter
Set @Sql = @Sql + ' and bb.BillNo = a.billno'+convert(varchar,@ColumnCount) + ') ' + @enter
Set @Sql = @Sql + 'From #tmpDetail a ' + @enter
Set @Sql = @Sql + 'Where a.FinVoucherMasterId is not null ' + @enter
Exec ( @sql )
print @sql
Set @ColumnCount = @ColumnCount - 1
End
End
-------------------------- 多栏帐显示
Set @SQlSelect = @SQlSelect + 'From #tmpDetail ' + @enter
Set @SQlSelect = @SQlSelect + 'Order By Fdate , Fyear , Fperiod ' + @enter
exec ( @SQlSelect )
Drop Table #tmpDetail
------解决方案--------------------
通过动态生成数据窗口的方式