如何将一张表查询出的奇偶列用不同的列名合并成一张表并且列名不变

[feifiv]

现在又一张表prod：
id text
1 a
2 b
3 c
4 d
5 e
6 f
我用：
select prod.id as id1,prod.text as text1 from prod where prod.id%2 =1
select prod.id as id2,prod.text as text2 from prod where prod.id%2 =0
分别查询出奇偶列，问题是如何把这个查询合并成一张表，并且列名(也就是分别起的别名)不变？
也就是结果为：
id1 text1 id2 text2
1 a 2 b
3 c 4 d
5 e 6 f
哪位大侠知道啊？

[老猫五号]

select id1,text1,id2,text2
from
(

select prod.id as id1,prod.text as text1,row_number() over (order by id1
) as rn from prod where prod.id%2 =1
) A,
(
select prod.id as id2,prod.text as text2,row_number() over (order by id2
) as rn from prod where prod.id%2 =0
)B
where A.rn = B.rn

[老猫五号]

select id1,text1,id2,text2
from
(

select prod.id as id1,prod.text as text1,row_number() over (order by prod.id) as rn from prod where prod.id%2 =1
) A,
(
select prod.id as id2,prod.text as text2,row_number() over (order by prod.id) as rn from prod where prod.id%2 =0
)B
where A.rn = B.rn

[feifiv]

忘记说了我用的是SQL2000，SQL2000好像不支持row_number()这个函数吧？

[老猫五号]

那就用临时表来实现，表中加一个自增的ID，也可以实现

[feifiv]

能写下具体的语句吗？

[dawugui]

--如果你能确保你的ID按顺序+1.

create table prod(id int,[text] varchar(10))
insert into prod values(1 ,'a')
insert into prod values(2 ,'b')
insert into prod values(3 ,'c')
insert into prod values(4 ,'d')
insert into prod values(5 ,'e')
insert into prod values(6 ,'f')
go

select max(case when id % 2 = 1 then id else null end) id1,
       max(case when id % 2 = 1 then [text] else null end) text1,
       max(case when id % 2 = 0 then id else null end) id2,
       max(case when id % 2 = 0 then [text] else null end) text2
from prod
group by (id - 1)/2

/*

id1         text1      id2         text2      
----------- ---------- ----------- ---------- 
1           a          2           b
3           c          4           d
5           e          6           f

（所影响的行数为 3 行）
*/

drop table prod

--如果你的ID不能确保顺序,则需要使用子查询生成一个序号列.

create table prod(id int,[text] varchar(10))
insert into prod values(1 ,'a')
insert into prod values(2 ,'b')
insert into prod values(3 ,'c')
insert into prod values(4 ,'d')
insert into prod values(5 ,'e')
insert into prod values(6 ,'f')
go

select max(case when px % 2 = 1 then id else null end) id1,
       max(case when px % 2 = 1 then [text] else null end) text1,
       max(case when px % 2 = 0 then id else null end) id2,
       max(case when px % 2 = 0 then [text] else null end) text2
from (select t.* , px = (select count(1) from prod where id < t.id) + 1 from prod t) m
group by (px - 1)/2

/*

id1         text1      id2         text2      
----------- ---------- ----------- ---------- 
1           a          2           b
3           c          4           d
5           e          6           f

（所影响的行数为 3 行）
*/

drop table prod

--如果是sql 2005,可将子查询改为:
select t.* , px = row_number() over(order by id) from tb t