查询的问题

[duanduan1122]

我有一张表：
id company telphoe
1 a1 123
2 a1 456
3 a2 678
4 a2 897

我想用一条查询语句得到下列结果：
id a1 a2
1 123 678
2 456 897

请问我的select 怎么写？

[愉快的登山者]

select A.id, A.telphoe as a1, B.telphoe as a2 from table1 as A, table1 as B
where A.company = 'a1' and B.company = 'a2' and A.id = B.id - 2

[cgsun]

select A.id, A.telphoe as a1, B.telphoe as a2 from table1 as A, table1 as B
where A.company = 'a1' and B.company = 'a2' and A.id = B.id - 2

[Jianli2004]

上面的办法不够通用

select identity(int,1,1) id, telphone as a1 into #2 from #1 where company = 'a1'
select identity(int,1,1) id, telphone as a2 into #3 from #1 where company = 'a2'

select a.id,a.a1,b.a2
from #2 a full join #3 b on a.id=b.id

[duanduan1122]

当我的表中company列有多条记录时怎么办呢？
上面的做法，不是太复杂了吗？
有简单的方法吗？

能实现的话马上给分！