62,046
社区成员
发帖
与我相关
我的任务
分享
set @Sql = 'alter table #temp add ['+@ColumnName+'] nvarchar(50) default null'
exec(@Sql)
FETCH NEXT FROM myCursor
set @Sql = 'alter table '+ @temp +' add [' + @ColumnName + '] nvarchar(50) default null'
set @Sql = 'alter table @temp add [' + @ColumnName + '] nvarchar(50) default null'
--定义全局临时表
create table #temp (id int identity,辅助项部门 nvarchar(100),客户 nvarchar (100),
项目名称 nvarchar (100),辅助项的销售业务类别 nvarchar (100),科目名称 nvarchar (100),
借方金额 float ,贷方金额 float)
--定义科目表
declare @tempTable table( 科目名称 nvarchar (100))
--定义科目表行数,行索引,定义列名,定义sql语句
declare @RowCount int, @RowIndex int ,@ColumnName nvarchar (50),@Sql nvarchar(1000)
--定义游标
declare myCursor Cursor for select * from @tempTable
--填充全局数据表
insert into #temp select * from( select
(select cDepName from Department where cDepCode=cdept_id) as '辅助项部门',
(select cCusName from Customer where cCusCode=ccus_id) as '客户',
(select citemname from fitemss97 where fitemss97.citemcode=GL_accvouch.citem_id) as '项目名称',
cDefine2 as '辅助项的销售业务类别',
(select ccode_name from code where code.ccode=GL_accvouch.ccode) as '科目名称',
md as '借方金额',
mc as '贷方金额' from GL_accvouch ) a
--填充科目表
insert into @tempTable select * from
(select
(select ccode_name from code where code.ccode=GL_accvouch.ccode) as '科目名称'
from GL_accvouch) aa
group by 科目名称
--取科目表行数
select @RowCount = count(*) from @tempTable
--打开索引,循环向全局表中插入新列
open myCursor
fetch next from myCursor into @ColumnName
while @@fetch_status = 0
begin
set @Sql = 'alter table #temp add ' + @ColumnName + ' nvarchar(50) default null'
print @Sql
exec(@sql)
fetch next from myCursor into @ColumnName
end
close myCursor
deallocate myCursor
--返回全局表
select * from #temp
set @Sql = 'alter table @temp add [' + @ColumnName + '] nvarchar(50) default null'
--楼主请及时结贴哦
--定义全局临时表
declare @temp table(id int identity,辅助项部门 nvarchar(100),客户 nvarchar (100),
项目名称 nvarchar (100),辅助项的销售业务类别 nvarchar (100),科目名称 nvarchar (100),
借方金额 float ,贷方金额 float)
--定义科目表
declare @tempTable table( 科目名称 nvarchar (100))
--定义科目表行数,行索引,定义列名,定义sql语句
declare @RowCount int, @RowIndex int ,@ColumnName nvarchar (50),@Sql nvarchar(1000)
--定义游标
declare myCursor Cursor for select * from @tempTable
--填充全局数据表
insert into @temp select * from( select
(select cDepName from Department where cDepCode=cdept_id) as '辅助项部门',
(select cCusName from Customer where cCusCode=ccus_id) as '客户',
(select citemname from fitemss97 where fitemss97.citemcode=GL_accvouch.citem_id) as '项目名称',
cDefine2 as '辅助项的销售业务类别',
(select ccode_name from code where code.ccode=GL_accvouch.ccode) as '科目名称',
md as '借方金额',
mc as '贷方金额' from GL_accvouch ) a
--填充科目表
insert into @tempTable select * from
(select
(select ccode_name from code where code.ccode=GL_accvouch.ccode) as '科目名称'
from GL_accvouch) aa
group by 科目名称
--取科目表行数
select @RowCount = count(*) from @tempTable
--打开索引,循环向全局表中插入新列
open myCursor
fetch next from myCursor into @ColumnName
while @@fetch_status = 0
begin
--该字段名称可能为数字开头的。所有要转换。
set @Sql = 'alter table @temp add [' + @ColumnName + '] nvarchar(50) default null'
--改的就是这句。
print @Sql
exec(@sql)
fetch next from myCursor into @ColumnName
end
close myCursor
deallocate myCursor
--返回全局表
select * from @temp