select * from (
select vchar,'jine1' as name,jine1 from temp
union all
select vchar,'jine2' as name,jine2 from temp
union all
select vchar,'jine3' as name,jine3 from temp
) as x
order by vchar,name
select vchar,'jine1' as name,jine1 from temp
union all
select vchar,'jine2' as name,jine2 from temp
union all
select vchar,'jine3' as name,jine3 from temp
) as x
order by vchar,name
解决方案 »
- 一个sql 触发器问题,请各位帮帮忙
- 关于事件探查器里的exec sp_execute
- 作业调度时间最小是1分钟,谁有办法让作业以秒来计算?
- 为何使用事务后存储过程的执行速度大大降低?
- 求条SQL语句!
- 存儲過程有時執行不完整(多人同時使用)
- 关于SQL逆向查询
- 请问SQLServer2005的版本号有9.0的吗?
- 输入字符串的格式不正确(说是有字符串不能转换成Datetime,但是我这里只有sys_create_date和sys_modify_date是datetime)
- 请教如何在扩展存储过程中使用WNetAddConnection2映射Novell服务器上的资源?
- 再问 如何读取sql表中字段的描述?
- 企业管理器连不到同局域网内的另一个Sql Server是什么原因?
Table1:
id var1 var2 var3 ..... (这里的1,2,3.....是ID的值)
name1 aa ii xx ……
name2 bb jj yy ……
name3 cc kk zz ……
想实现如楼主所说的哪种形式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
--嘻嘻,我的代码是抄的竹之草的
--哈哈。