标题是否符合网友的问题宗旨，另外讨论，暂且如此。想了妥解问题，还得看原讨论题。
这是一个网上的问题如下，
;with temp as
(
select '63738893' repair_no,'20190504' report_date,'hes2418819040700003'service_sheet_no,null sno,'467769309410' rno union all
select '63738893' repair_no,'20190504' report_date,'hes2418819040700003'service_sheet_no,'467769309411' sno,null rno union all
select '63738793' repair_no,'20190508' report_date,'hes2418819040700003'service_sheet_no,'467769309411' sno,null rno
)select * from temp
--以上原始数据
--以下想要的结果
;with temp as
(
select '63738893' repair_no,'20190504' report_date,'hes2418819040700003'service_sheet_no,'467769309411' sno,'467769309410' rno union all
--select '63738893' repair_no,'20190504' report_date,'hes2418819040700003'service_sheet_no,'467769309411' sno,null rno union all
select '63738793' repair_no,'20190508' report_date,'hes2418819040700003'service_sheet_no,'467769309411' sno,null rno
)select * from temp
下面是insus.net实现方法。另创建一张临时表，比网友的数据表添加一个字段id，删除一些与问题无关的字段。
在ms sql server 2017版本中实现。
insus.net的方法是使用row_number和partition时行分组：
先分析一列[sno]，看看：
;with s as
(
select row_number() over(partition by [repair_no] order by [id],[sno]) as [row_num],
[id],
[repair_no],
[sno]
from #t
where [sno] is not null
)
select [row_num],[id],[repair_no],[sno] from s;
另一列[rno]:
;with
r as
(
select row_number() over(partition by [repair_no] order by [id],[rno]) as [row_num],
[id],
[repair_no],
[rno]
from #t
where [rno] is not null
)
select [row_num],[id],[repair_no],[rno] from r;
以上加个id列，主要是为了让大家看到它的排序，拿到的是第一列非空的值。网友的问题，直接按[repair_no]排序即可。
下面代码是把上面2列合并在一起。
;with s as
(
select row_number() over(partition by [repair_no] order by [id],[sno]) as [row_num],
[id],
[repair_no],
[sno]
from #t
where [sno] is not null
),
r as
(
select row_number() over(partition by [repair_no] order by [id],[rno]) as [row_num],
[id],
[repair_no],
[rno]
from #t
where [rno] is not null
)
select s.[repair_no],[sno],[rno] from s
inner join r on (s.[repair_no] = r.[repair_no])
where s.[row_num] = 1 and r.row_num = 1;
使用色彩来引示可以看到明白：
把以上方法去解决网友的问题，却得到另外一个结果：
对比一下，原来空值也应该有，就是当一个值都没有时，才用空值填充。
看来得改写一下程序，创建临时表，存储结果。
2个字段分别处理，把结果merge来合并至临时表中：
create table #ok_result([repair_no] int,[sno] nvarchar(50),[rno] nvarchar(50))
;with temp as
(
select '63738893' repair_no,'20190504' report_date,'hes2418819040700003'service_sheet_no,null sno,'467769309410' rno union all
select '63738893' repair_no,'20190504' report_date,'hes2418819040700003'service_sheet_no,'467769309411' sno,null rno union all
select '63738793' repair_no,'20190508' report_date,'hes2418819040700003'service_sheet_no,'467769309411' sno,null rno
),s as
(
select row_number() over(partition by [repair_no] order by repair_no) as [row_num],
[repair_no],
[sno]
from temp
where [sno] is not null
)
merge #ok_result as target
using (select [repair_no],[sno] from s where [row_num] = 1) as source
on (target.[repair_no] = source.[repair_no])
when matched then
update set target.[sno] = source.[sno]
when not matched by target then
insert ([repair_no],[sno]) values ([repair_no],[sno]);
;with temp as
(
select '63738893' repair_no,'20190504' report_date,'hes2418819040700003'service_sheet_no,null sno,'467769309410' rno union all
select '63738893' repair_no,'20190504' report_date,'hes2418819040700003'service_sheet_no,'467769309411' sno,null rno union all
select '63738793' repair_no,'20190508' report_date,'hes2418819040700003'service_sheet_no,'467769309411' sno,null rno
),r as
(
select row_number() over(partition by [repair_no] order by repair_no) as [row_num],
[repair_no],
[rno]
from temp
where [rno] is not null
)
merge #ok_result as target
using (select [repair_no],[rno] from r where [row_num] = 1) as source
on (target.[repair_no] = source.[repair_no])
when matched then
update set target.[rno] = source.[rno]
when not matched by target then
insert ([repair_no],[sno]) values ([repair_no],[rno]);
select [repair_no],[sno],[rno] from #ok_result