把查询出来的的数据一列分为两列的SQL语句怎么写啊？请各位高手帮帮忙!

现在表中查出来如下： 
  VALUE ID 
  111 2 
  234 3 
  312 3 
  65 3 
  78 2 
  541 2 
现在我需要把VALUE这一列根据ID的值分开，想得到的结果如下： 
  
  VALUE2 VALUE3 
  111 234 
  78 312 
  541 65 
这个SQL语句怎么写啊？
环境是在sql server2000中

SQL code

select 
  max(case px when 1 then value else 0 end) value2,
  max(case px when 2 then value else 0 end) value3
from
(
  select * , px = (select count(1) from tb where id = t.id and value < t.value) + 1 from tb t
) m
group by id

我一楼回复错了.

SQL code

select m.value value1 , n.value value2
from
(select * , px = (select count(1) from tb where id = t.id and value < t.value) + 1 from tb t) m,
(select * , px = (select count(1) from tb where id = t.id and value < t.value) + 1 from tb t) n
where m.px = n.px

--1,2楼都错了,这个对了.

SQL code

create table tb(VALUE int,   ID int)
insert into tb values(111 ,   2 )
insert into tb values(234 ,   3 )
insert into tb values(312 ,   3 )
insert into tb values(65  ,   3 )
insert into tb values(78  ,   2 )
insert into tb values(541 ,   2 )
go

select m.value value1 , n.value value2
from
(select * , px = (select count(1) from tb where id = 2 and id = t.id and value < t.value) + 1 from tb t where id = 2) m,
(select * , px = (select count(1) from tb where id = 3 and id = t.id and value < t.value) + 1 from tb t where id = 3) n
where m.px = n.px 

drop table tb

/*

value1      value2      
----------- ----------- 
111         234
78          65
541         312

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

--3楼为sql 2000的写法,这个是sql 2005的.

SQL code

create table tb(VALUE int,   ID int)
insert into tb values(111 ,   2 )
insert into tb values(234 ,   3 )
insert into tb values(312 ,   3 )
insert into tb values(65  ,   3 )
insert into tb values(78  ,   2 )
insert into tb values(541 ,   2 )
go

select m.value value1 , n.value value2 from
(select * ,px = row_number() over(partition by id order by value) from tb ) m ,
(select * ,px = row_number() over(partition by id order by value) from tb ) n
where m.id = 2 and n.id = 3 and m.px = n.px5

drop table tb

/*
value1      value2
----------- -----------
78          65
111         234
541         312

(3 行受影响)

*/

奇怪的需求

这个我感觉不是需求奇怪.应该是数据库设计的时候没有设计好吧...
我也遇到这样的问题.