declare @colname varchar(40) declare cur cursor for select [name] from t_codes where type=217 open cur fetch next from cur into @colname while @@fetch_status=0 begin exec('alter table #t add '+@colname+' varchar(20) ') fetch next from cur into @colname end
create table #t(商品编号 varchar(300),商品名称 varchar(300),包装规格 decimal(18,4),整单位 varchar(50),散单位 varchar(50)) declare @colname varchar(4000),@sql varchar(8000) declare cur cursor for select [name] from t_codes where type=217 open cur fetch next from cur into @colname while @@fetch_status=0 begin exec('alter table #t add '+@colname+' varchar(20) ') fetch next from cur into @colname end --上面增加了所有列名后,下面插入数据 set @colname='' select @colname=@colname+','+[name] from t_codes where type=217set @sql='insert into #t(商品编号,商品名称,包装规格,整单位,散单位',+@colname+')' set @sql=@sql+' select a.code ,a.name ,a.packstandard , c.name ,b.name '+@colname+' from t_bs_product as a left join t_codes b on a.bs_unit_dispel_id =b.id left join t_codes c on a.bs_unit_full_id =c.id ' exec(@sql)
create table #t(商品编号 varchar(300),商品名称 varchar(300),包装规格 decimal(18,4),整单位 varchar(50),散单位 varchar(50)) declare @colname varchar(4000),@sql varchar(8000) declare cur cursor for select [name] from t_codes where type=217 open cur fetch next from cur into @colname while @@fetch_status=0 begin exec('alter table #t add '+@colname+' varchar(20) ') fetch next from cur into @colname end --上面增加了所有列名后,下面插入数据 set @colname='' select @colname=@colname+','+[name] from t_codes where type=217set @sql='insert into #t(商品编号,商品名称,包装规格,整单位,散单位'+@colname+')' set @sql=@sql+' select a.code ,a.name ,a.packstandard , c.name ,b.name '+@colname+' from t_bs_product as a left join t_codes b on a.bs_unit_dispel_id =b.id left join t_codes c on a.bs_unit_full_id =c.id ' exec(@sql)
wgsasd311(自强不息)的再一次回复还是没有实现最后一步,因为最后哪一步是从t_bs_product_datile里查出数据作为列的数据,而不是从t_bs_product里取出,两个表之间的相连是select a.code ,a.name ,a.packstandard , c.name ,b.name '+@colname+' from t_bs_product as a left join t_codes b on a.bs_unit_dispel_id =b.id left join t_codes c on a.bs_unit_full_id =c.id left join t_bs_product_detail d on d.bs_product_id=a.id 但这个'+@colname+'我不会写代码
create table #t(商品编号 varchar(300),商品名称 varchar(300),包装规格 decimal(18,4),整单位 varchar(50),散单位 varchar(50)) declare @colname varchar(4000),@sql varchar(8000) declare cur cursor for select [name] from t_codes where type=217 open cur fetch next from cur into @colname while @@fetch_status=0 begin exec('alter table #t add '+@colname+' varchar(20) ') fetch next from cur into @colname end --上面增加了所有列名后,下面插入数据 declare @col varchar(400) set @colname='' select @colname=@colname+','+[name] from t_codes where type=217 --因为最后哪一步是从t_bs_product_datile里查出数据作为列的数据,而不是从t_bs_product里取出,两个表之间的相连是select set @col='' select @col=@col+',d.'+[name] from t_codes where type=217set @sql='insert into #t(商品编号,商品名称,包装规格,整单位,散单位'+@colname+')' set @sql=@sql+' select a.code ,a.name ,a.packstandard , c.name ,b.name '+@col+' from t_bs_product as a left join t_codes b on a.bs_unit_dispel_id =b.id left join t_codes c on a.bs_unit_full_id =c.id left join t_bs_product_detail d on d.bs_product_id=a.id ' exec(@sql)
declare cur cursor for
select [name] from t_codes where type=217
open cur
fetch next from cur into @colname
while @@fetch_status=0
begin
exec('alter table #t add '+@colname+' varchar(20) ')
fetch next from cur into @colname
end
create table #t(商品编号 varchar(300),商品名称 varchar(300),包装规格 decimal(18,4),整单位 varchar(50),散单位 varchar(50))
declare @colname varchar(4000),@sql varchar(8000)
declare cur cursor for
select [name] from t_codes where type=217
open cur
fetch next from cur into @colname
while @@fetch_status=0
begin
exec('alter table #t add '+@colname+' varchar(20) ')
fetch next from cur into @colname
end
--上面增加了所有列名后,下面插入数据
set @colname=''
select @colname=@colname+','+[name] from t_codes where type=217set @sql='insert into #t(商品编号,商品名称,包装规格,整单位,散单位',+@colname+')'
set @sql=@sql+' select a.code ,a.name ,a.packstandard ,
c.name ,b.name '+@colname+' from t_bs_product as a
left join t_codes b on a.bs_unit_dispel_id =b.id
left join t_codes c on a.bs_unit_full_id =c.id '
exec(@sql)
create table #t(商品编号 varchar(300),商品名称 varchar(300),包装规格 decimal(18,4),整单位 varchar(50),散单位 varchar(50))
declare @colname varchar(4000),@sql varchar(8000)
declare cur cursor for
select [name] from t_codes where type=217
open cur
fetch next from cur into @colname
while @@fetch_status=0
begin
exec('alter table #t add '+@colname+' varchar(20) ')
fetch next from cur into @colname
end
--上面增加了所有列名后,下面插入数据
set @colname=''
select @colname=@colname+','+[name] from t_codes where type=217set @sql='insert into #t(商品编号,商品名称,包装规格,整单位,散单位'+@colname+')'
set @sql=@sql+' select a.code ,a.name ,a.packstandard ,
c.name ,b.name '+@colname+' from t_bs_product as a
left join t_codes b on a.bs_unit_dispel_id =b.id
left join t_codes c on a.bs_unit_full_id =c.id '
exec(@sql)
c.name ,b.name '+@colname+' from t_bs_product as a
left join t_codes b on a.bs_unit_dispel_id =b.id
left join t_codes c on a.bs_unit_full_id =c.id
left join t_bs_product_detail d on d.bs_product_id=a.id
但这个'+@colname+'我不会写代码
declare @colname varchar(4000),@sql varchar(8000)
declare cur cursor for
select [name] from t_codes where type=217
open cur
fetch next from cur into @colname
while @@fetch_status=0
begin
exec('alter table #t add '+@colname+' varchar(20) ')
fetch next from cur into @colname
end
--上面增加了所有列名后,下面插入数据
declare @col varchar(400)
set @colname=''
select @colname=@colname+','+[name] from t_codes where type=217
--因为最后哪一步是从t_bs_product_datile里查出数据作为列的数据,而不是从t_bs_product里取出,两个表之间的相连是select
set @col=''
select @col=@col+',d.'+[name] from t_codes where type=217set @sql='insert into #t(商品编号,商品名称,包装规格,整单位,散单位'+@colname+')'
set @sql=@sql+' select a.code ,a.name ,a.packstandard ,
c.name ,b.name '+@col+' from t_bs_product as a
left join t_codes b on a.bs_unit_dispel_id =b.id
left join t_codes c on a.bs_unit_full_id =c.id
left join t_bs_product_detail d on d.bs_product_id=a.id '
exec(@sql)