如果知道需要显示的确切的数据数量,那么直接在sql的select语句中这样写:
select 1 as id1, (select name from table where id=1)as name1,(select age from table where id=1)as age1,(select gender from table where id=1)as gender1,
2 as id2, (select name from table where id=2)as name2,(select age from table where id=2)as age2,(select gender from table where id=2)as gender2,
3 as id3, (select name from table where id=3)as name3,(select age from table where id=3)as age3,(select gender from table where id=3)as gender3
from table
如果数据量不确定,则可以用游标做一个对原表的遍历,这样写(假设记录数最多100条):
create table #result (id1 int ,name1 varchar(10),age1 int , gender1 char(2)......id100 int ,name100 varchar(10),age100 int , gender100 char(2))
declare @id int, @name varchar(10), @age int , @gender char(2),@i int, @c varchar(5),
@cId varchar(5), @cAge varchar(3)
set @i=0
set @c='0'
declare mycur cursor for select id,name,age,gender from table order by id
open mycur
fetch next from mycur into @id , @name , @age , @gender
while (@@fetch_status=0)
begin
set @i=@i+1
set @c=convert(varchar(5),@i)
exec('insert into #result (id'+@c+',name'+@c+',age'+@c+',gender'+@c+') values ('+@cId+','''+@name+''','+@cAge+',‘’‘+@gender+’‘’)‘)
fetch next from mycur into @id , @name , @age , @gender
end
close mycur
deallocate mycur
select * from #result
温馨提示:答案为网友推荐,仅供参考