sql 记录合并的问题

[PB菜鸟]

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'

如何实现数据按下边方式显示。
col1 col2 col3 col4 col5
A1 B1 C1 B4 C4
A2 B2 C2 B3 C3

[中国风]

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
*/

[叶子]

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
*/

[PB菜鸟]

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，用上边的实现了 不知道效率如何。

[中国风]

[Quote=引用 3 楼 smilysoft 的回复:]

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，用上边的实现了 不知道效率如何。
[/Quote]
生成临时表处理

[中国风]

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