合并问题,再次请教各位:)
上一类似帖已结,现想要的结果有些变动,再次请教大家:)

如下数据：
kid lid f3 f4 f5 f6
 
1 2 a NULL NULL NULL
1 2 NULL e NULL NULL
1 2 b NULL NULL e
1 2 q b NULL j
2 9 u x n b 
1 3 k f d c

现要将kid和lid均相同的行进行合并
合并后的数据为： 
kid lid f3 f4 f5 f6
 
1 2 a e NULL e  
2 9 u x n b 
1 3 k f d c


亦即是kid,lid相同的,最靠前的一行的某一字段，
  如果不为空，则保留该值;
  如果为空，则有两种取值:一是都为空,则取值NULL;二是不全为空,则取最靠近的行且不为空的值．

------解决方案--------------------
create table A(kid int,lid int,f3 varchar(10),f4 varchar(10),f5 varchar(10),f6 varchar(10)) 
insert into A values(1,2, 'a' ,NULL, NULL,NULL ) 
insert into A values(1,2, NULL, 'e' , NULL,NULL ) 
insert into A values(1,2, 'b' , NULL ,NULL,'e')
insert into A values(1,2, 'q' , 'b' ,NULL, 'j' ) 
insert into A values(2,9, 'u' , 'x' ,'n', 'b' )
insert into A values(1,3, 'k' , 'f' , 'd','c') 
go 

select identity(int,1,1) as id,* into #T from A

select distinct 
a.kid,a.lid, 
f3=(select top 1 f3 from #T where kid=a.kid and lid=a.lid and f3 is not null order by id), 
f4=(select top 1 f4 from #T where kid=a.kid and lid=a.lid and f4 is not null order by id), 
f5=(select top 1 f5 from #T where kid=a.kid and lid=a.lid and f5 is not null order by id),
f6=(select top 1 f6 from #T where kid=a.kid and lid=a.lid and f6 is not null order by id)
from 
#T a 
go 
drop table A,#T
------解决方案--------------------
create table test(kid int,lid int,f3 varchar(8),f4 varchar(8),f5 varchar(8),f6 varchar(8)) 
insert into test values(1,2,'a' ,NULL,NULL,NULL) 
insert into test values(1,2,NULL,'e' ,NULL,NULL) 
insert into test values(1,2,'b' ,NULL,NULL,'e' )
insert into test values(1,2,'q' ,'b' ,NULL,'j' )
insert into test values(2,9,'u' ,'x' ,'n' ,'b' ) 
insert into test values(1,3,'k' ,'f' ,'d' ,'c' ) 
go 

select identity(int,1,1) as id,* into # from test 

select distinct 
t.kid,t.lid, 
f3=(select top 1 f3 from # where kid=t.kid and lid=t.lid and f3 is not null order by id), 
f4=(select top 1 f4 from # where kid=t.kid and lid=t.lid and f4 is not null order by id), 
f5=(select top 1 f5 from # where kid=t.kid and lid=t.lid and f5 is not null order by id),
f6=(select top 1 f6 from # where kid=t.kid and lid=t.lid and f6 is not null order by id)
from 
# t 
go 

/* 
kid lid f3 f4 f5 f6
----------- ----------- -------- -------- -------- -------- 
1 2 a e NULL e
1 3 k f d c
2 9 u x n b
*/ 


drop table test,# 
go
------解决方案--------------------
create table tb(kid int,lid int,f3 varchar(10),f4 varchar(10),f5 varchar(10),f6 varchar(10))
insert into tb values(1 , 2 , 'a' , NULL , NULL, NULL) 
insert into tb values(1 , 2 , NULL, 'e' , NULL, NULL) 
insert into tb values(1 , 2 , 'b' , NULL , NULL, 'e')