一个超级难的行合并
有一个表T1，在T1中有两个列C1,C2，表中的数据如下
C1         C2

1             1
4             NULL
NULL       5
7             NULL
NULL       9
12           12
14           NULL
NULL       16

我想通过一个select语句来得到下面的结果：

C1         C2

1             1
4             5
7             9
12           12
14           16

请问这个select语句怎么写？

------解决方案--------------------

declare @t table (
C1 int,
C2 int
)

insert @t select
1 , 1
union all select
4 , NULL
union all select
NULL , 5
union all select
7 , NULL
union all select
NULL , 9
union all select
12 , 12
union all select
14 , NULL
union all select
NULL , 16

select IDENTITY(int,1,1) as id,*
into #
from @t

select C1,C2 from # where C1 is not null and C2 is not null
union all
select T2.C1,T1.C2 from # t1 full join # t2
on (select count(*) from # where C1 is null and Id <=T1.Id)=(select count(*) from # where C2 is null and Id <=T2.Id)
where T1.C1 is null
and T2.C2 is null

drop table #

--结果
C1 C2
----------- -----------
1 1
12 12
4 5
7 9
14 16

（所影响的行数为 5 行）


------解决方案--------------------
按照楼主的排序

declare @t table (
C1 int,
C2 int
)

insert @t select
1 , 1
union all select
4 , NULL
union all select
NULL , 5
union all select
7 , NULL
union all select
NULL , 9
union all select
12 , 12
union all select
14 , NULL
union all select
NULL , 16
union all select
17 , Null --加多一行

select IDENTITY(int,1,1) as id,*
into #
from @t

select C1,C2
from (
select C1,C2,Id from # where C1 is not null and C2 is not null
union all
select T2.C1,T1.C2,isnull(T1.id,T2.Id) as Id from # t1 full join # t2
on (select count(*) from # where C1 is null and Id <=T1.Id)=(select count(*) from # where C2 is null and Id <=T2.Id)
where T1.C1 is null
and T2.C2 is null
) as t
order by id

drop table #

--结果
C1 C2
----------- -----------
1 1
4 5
7 9
12 12
14 16
17 NULL

（所影响的行数为 6 行）


------解决方案--------------------
select id = identity(int , 1,1) ,c1 into tb1 from t1 where c1 is not null
select id = identity(int , 1,1) ,c2 into tb2 from t1 where c2 is not null
--查询
select c1,c2 from tb1 , tb2 where t1.id = t2.id
--更改
drop table t1
select c1,c2 from tb1 , tb2 into t1 where t1.id = t2.id


------解决方案--------------------
--测试数据：@T1
declare @T1 table(C1 int,C2 int)
insert @T1
select 1,1 union all