USE tempdb
go
create table a  
( a varchar(20) not null ,
b varchar (20) not null,
c varchar (20) null )
insert into a  
select 'A1','B1','C1' UNION  
SELECT 'A2','B2','C2' UNION  
SELECT 'A2','B3','C3' UNION
SELECT 'A1','B4','C4'
go

;with t1
as
(select row_number()over(PARTITION by  a order by a) as rn,* from a)
select a.a,a.b,a.c,b.b,b.c 
from t1 as a left join t1 as b on a.a=b.a  and a.rn=b.rn-1
where  a.rn%2=1

/*
a    b    c    b    c
A1    B1    C1    B4    C4
A2    B2    C2    B3    C3
*/

------解决方案--------------------
SQL code

select 
c.a as col1,c.b as col2,c.c as col3,b.b as col4,b.c as col5 
from a c left join a b on c.a=b.a where b.b>c.b
/*
col1                 col2                 col3                 col4                 col5
-------------------- -------------------- -------------------- -------------------- --------------------
A1                   B1                   C1                   B4                   C4
A2                   B2                   C2                   B3                   C3
*/

------解决方案--------------------
探讨


SELECT  c.a,c.b,c.c,d.b,d.c from 
(SELECT A.a,A.b,a.c from a
where a.B='B1'
) AS C ,

(select A.A,A.B,A.C
FROM A 
WHERE A.B='B4') AS D
用的是sql 2000，用上边的实现了 不知道效率如何。


------解决方案--------------------
SQL code
SQL2000这样用


USE tempdb
go
create table a  
( a varchar(20) not null ,
b varchar (20) not null,
c varchar (20) null )
insert into a  
select 'A1','B1','C1' UNION  
SELECT 'A2','B2','C2' UNION  
SELECT 'A2','B3','C3' UNION
SELECT 'A1','B4','C4'
go

select rn=identity(int,1,1),a,b,c  into # from a

select a.a,a.b,a.c,b.b,b.c 
from 
(select a,b,c,rn=(select COUNT(*) from # where a=a.a and rn<=a.rn) from # as  a) as a
left join (select a,b,c,rn=(select COUNT(*) from # where a=a.a and rn<=a.rn) from # as  a) as b on a.a=b.a and a.rn=b.rn-1
where a.rn%2=1