请求一列转行的sql

[xcwlbl]

原表为 t1
name d1 d2......d960
a 1 2...... 960
b 1 2...... 960
如何转为
t2
name d
a 1
a 2
a 3
...
a 960
b 1
b 2
...
b 960


我原写过
select name ,d1 from t1
union select name ,d2 from t1
...
union select name ,d960 from t1
但特慢，不知有无更好的sql，谢谢！

[jinjazz]

好像只能用union连接

[xluzhong]

O_O什么表？

[zjcxc]

--如果你不需要去重复的话,改用:

select name ,d1 from t1
union all
select name ,d2 from t1
...
union all
select name ,d960 from t1


--union 会对结果去重复处理

[cutelion]

UP

[子陌红尘]

只能用UNION和UNION ALL。

[xcwlbl]

若我想在存储过程中写,该sql岂不是太长,又该如何处理?

[zjcxc]

sql长怕什么? 效率高就行了

[xiaos139]

Declare @i int, @sql varchar(100)
Set @i = 1
while @i < = 960
begin
Set @sql = 'insert into t2 select top 1 ''a'' ,' + Col_name(Object_id('t1'),@i ) + ' from t1'
Exec(@sql)
Set @i = @i + 1
end

Declare @i int, @sql varchar(100)
Set @i = 1
while @i < = 960
begin
Set @sql = 'insert into t2 select top 1 ''b'' ,' + Col_name(Object_id('t1'),@i ) + ' from t1'
Exec(@sql)
Set @i = @i + 1
end
在公司的服務器上速度還不錯

[lishengyu]

一种猜测
也许分段处理吧　也不是分得越多越好，也不是越少越好，有一个合适的值　，自已找吧

select name ,d1 into #a from t1
union select name ,d2 from t1
...
union select name ,d500 from t1

select name ,d1 into #b from t1
union select name ,d2 from t1
...
union select name ,d960 from t1

select * from #a
union
select 8 from #b

[lishengyu]

中间一行　union select name ,d2 from t1　改为
　　　　　　　union select name ,d501 from t1