问题描述: 有表Table2,其结构及内容如下: Table2:id name1 name2 name3 ……(列有很多) 1 aa bb cc …… 2 ii jj kk …… 3 xx yy zz …… …………………………………………有表Table1,其结构如下: Table1: id variable现在想把Table2中的除id以外的字段名(注意,是字段名)填入Table1中的id, 从Table2中提取某一行(先假设是id为1的那一行吧)中与每个字段名对应的内容填入Table1的variable.以id为1的那行为例,就是要达到如下的效果: Table1: id variable name1 aa name2 bb name3 cc ………………. 我的解决方案:declare @vVariable varchar(2000),@var varchar(2000) --中間值的變量 declare @sql nvarchar(4000) -- 動態sql declare @insertSql varchar(4000) -- 插入臨時表的sql set @vVariable = ' ' if object_id('tempdb.dbo.#temp1') is not null drop table #temp1 select identity(int) id,name into #temp1 from syscolumns where id = object_id('Table2') and name <> 'id' order by colid if object_id('tempdb.dbo.#temp2') is null -- 創建臨時表#temp2 create table #temp2 ( id int identity, variable varchar(10) ) else truncate table #temp2select @vVariable = @vVariable + '+'',''+' + name from syscolumns where id = object_id('Table2') and name <> 'id' order by colid set @vVariable = right(@vVariable,len(@vVariable) - 6) set @sql = 'select @var = '+@vVariable+' from Table2 where id = 1' exec sp_executesql @sql,N'@var varchar(2000) output',@var output -- 組成以逗號分格的變量@varset @insertSql='insert into #temp2 values('''+REPLACE(@var,',',''') insert into #temp2 values(''')+''')' exec (@insertSql) -- 根據變量中的逗號分格插入臨時表insert into Table1(id,variable) select name,variable from #temp1,#temp2 where #temp1.id = #temp2.idselect * from Table1
斑竹,干脆把如下格式的也写了:Table1: id var1 var2 var3 ..... (这里的1,2,3.....是ID的值) name1 aa ii xx …… name2 bb jj yy …… name3 cc kk zz …… ………………. 回复人: zhuzhichao(竹之草) ( ) 信誉:100 2002-1-30 15:13:39 得分:0
按照Haiwer(海阔天空)的格式也寫好了. declare @vVariable varchar(2000),@var varchar(2000) --中間值的變量 declare @sql nvarchar(4000) -- 動態sql declare @insertSql varchar(4000) -- 插入臨時表的sql declare @alterSql varchar(100) -- 修改臨時表#temp1的sql declare @updateSql varchar(200) -- 更新臨時表#temp1的數據sql declare @i int,@count int -- Table2的紀錄數 select @count = count(1) from Table2; if @count = 0 begin print 'No row' return end if @count > 254 begin print 'Too many rows' return end set @vVariable = ' ' select @vVariable = @vVariable + '+'',''+' + name from syscolumns where id = object_id('Table2') and name <> 'id' order by colid set @vVariable = right(@vVariable,len(@vVariable) - 6)if object_id('tempdb.dbo.#temp1') is not null drop table #temp1 select identity(int) id,name into #temp1 from syscolumns where id = object_id('Table2') and name <> 'id' order by colid set @i = 1 while (@i <= @count) begin if object_id('tempdb.dbo.#temp2') is null -- 創建臨時表#temp2 create table #temp2 ( id int identity, variable varchar(10) ) else truncate table #temp2 set @sql = 'select @var = '+@vVariable+' from Table2 where id = ' + convert(varchar(3),@i) exec sp_executesql @sql,N'@var varchar(2000) output',@var output -- 組成以逗號分格的變量@var set @insertSql='insert into #temp2 values('''+REPLACE(@var,',',''') insert into #temp2 values(''')+''')' exec (@insertSql) -- 根據變量中的逗號分格插入臨時表
作 者: zhuzhichao (竹之草)
等 级:
信 誉 值: 100
所属论坛: MS-SQL Server 基础类
问题点数: 300
回复次数: 60
发表时间: 2002-1-30 10:03:24
问题描述:
有表Table2,其结构及内容如下:
Table2:id name1 name2 name3 ……(列有很多)
1 aa bb cc ……
2 ii jj kk ……
3 xx yy zz ……
…………………………………………有表Table1,其结构如下:
Table1:
id variable现在想把Table2中的除id以外的字段名(注意,是字段名)填入Table1中的id,
从Table2中提取某一行(先假设是id为1的那一行吧)中与每个字段名对应的内容填入Table1的variable.以id为1的那行为例,就是要达到如下的效果:
Table1:
id variable
name1 aa
name2 bb
name3 cc
……………….
我的解决方案:declare @vVariable varchar(2000),@var varchar(2000) --中間值的變量
declare @sql nvarchar(4000) -- 動態sql
declare @insertSql varchar(4000) -- 插入臨時表的sql
set @vVariable = ' '
if object_id('tempdb.dbo.#temp1') is not null
drop table #temp1
select identity(int) id,name into #temp1 from syscolumns where id = object_id('Table2') and name <> 'id' order by colid if object_id('tempdb.dbo.#temp2') is null -- 創建臨時表#temp2
create table #temp2
(
id int identity,
variable varchar(10)
)
else
truncate table #temp2select @vVariable = @vVariable + '+'',''+' + name from syscolumns where id =
object_id('Table2') and name <> 'id' order by colid
set @vVariable = right(@vVariable,len(@vVariable) - 6)
set @sql = 'select @var = '+@vVariable+' from Table2 where id = 1'
exec sp_executesql @sql,N'@var varchar(2000) output',@var output -- 組成以逗號分格的變量@varset @insertSql='insert into #temp2 values('''+REPLACE(@var,',',''')
insert into #temp2 values(''')+''')'
exec (@insertSql) -- 根據變量中的逗號分格插入臨時表insert into Table1(id,variable)
select name,variable from #temp1,#temp2
where #temp1.id = #temp2.idselect * from Table1
回复人: Haiwer(海阔天空) ( ) 信誉:100 2002-1-30 14:13:42 得分:6
斑竹,干脆把如下格式的也写了:Table1:
id var1 var2 var3 ..... (这里的1,2,3.....是ID的值)
name1 aa ii xx ……
name2 bb jj yy ……
name3 cc kk zz ……
……………….
回复人: zhuzhichao(竹之草) ( ) 信誉:100 2002-1-30 15:13:39 得分:0
按照Haiwer(海阔天空)的格式也寫好了.
declare @vVariable varchar(2000),@var varchar(2000) --中間值的變量
declare @sql nvarchar(4000) -- 動態sql
declare @insertSql varchar(4000) -- 插入臨時表的sql
declare @alterSql varchar(100) -- 修改臨時表#temp1的sql
declare @updateSql varchar(200) -- 更新臨時表#temp1的數據sql
declare @i int,@count int -- Table2的紀錄數
select @count = count(1) from Table2;
if @count = 0
begin
print 'No row'
return
end
if @count > 254
begin
print 'Too many rows'
return
end
set @vVariable = ' '
select @vVariable = @vVariable + '+'',''+' + name from syscolumns where id =
object_id('Table2') and name <> 'id' order by colid
set @vVariable = right(@vVariable,len(@vVariable) - 6)if object_id('tempdb.dbo.#temp1') is not null
drop table #temp1
select identity(int) id,name into #temp1 from syscolumns where id = object_id('Table2') and name <> 'id' order by colid set @i = 1
while (@i <= @count)
begin
if object_id('tempdb.dbo.#temp2') is null -- 創建臨時表#temp2
create table #temp2
(
id int identity,
variable varchar(10)
)
else
truncate table #temp2 set @sql = 'select @var = '+@vVariable+' from Table2 where id = ' + convert(varchar(3),@i)
exec sp_executesql @sql,N'@var varchar(2000) output',@var output -- 組成以逗號分格的變量@var set @insertSql='insert into #temp2 values('''+REPLACE(@var,',',''')
insert into #temp2 values(''')+''')'
exec (@insertSql) -- 根據變量中的逗號分格插入臨時表
set @alterSql = 'alter table #temp1 add var' + convert(varchar(3),@i) + ' varchar(10)'
exec (@alterSql) -- 增加臨時表的字段
set @updateSql = 'update #temp1 set var' + convert(varchar(3),@i) + ' = variable from #temp2 '
+'where #temp1.id = #temp2.id'
exec (@updateSql) -- 更新臨時表的數據
set @i = @i + 1
end
alter table #temp1 drop column id
select * from #temp1