sql 记录合并的有关问题

sql 记录合并的问题
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 B4C4
A2 B2 C2 B3C3

[解决办法]

SQL code

USE tempdbgocreate 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' UNIONSELECT 'A1','B4','C4'go;with t1as(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-1where  a.rn%2=1/*a    b    c    b    cA1    B1    C1    B4    C4A2    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                   C4A2                   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 tempdbgocreate 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' UNIONSELECT 'A1','B4','C4'goselect rn=identity(int,1,1),a,b,c  into # from aselect 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 aleft 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-1where a.rn%2=1