求助：多栏式明细账的生成方法

[jaychensusu]

1、说明：多栏明细显示的各例，是根据用户设置显示生成，所以各列明细项目是不固定的。
2、要求：麻烦高手提供编制的方法，最好有代码复制到回贴中。谢谢！

[gzlx]

一般通过组合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) + ' = ''' +@BillNo + '''' 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) + ' = ''' +@BillNo + '''' 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

[pcwe2002]

通过动态生成数据窗口的方式

string ls_sql,ls_syntax,ls_error 
　　ls_sql= 'select * from department' 
　　ls_syntax = sqlca.SyntaxFromSQL(ls_sql,'style(type=grid)',ls_error) 
　　if len(ls_error) >0 then 
　　　messagebox('Error','SyntaxFromSQL Error:~r'+ls_error) 
　　else 
　　　dw_new.create(ls_syntax,ls_error) 
　　　if len(ls_error) >0 then 
　　　　MessageBox("Error", "Create have these errors: ~r" + ls_error) 
　　　else 
　　　　dw_new.settransobject(sqlca) 
　　　　dw_new.retrieve() 
　　　end if 
　　end if