如何在不丢失其他行数据的情况下从多列中删除空值

Question

假设我有两个类似的列

basis_adjutment     participant_pct
123                 null
456                 null
789                 null
null                5678
null                90465
null                25457

我想要像一样的结果输出

basis_adjutment     participant_pct
123                 5678
456                 90465
789                 25457

有人能建议我如何在蜂箱中获得这个吗？我应该使用的哪个功能

Answer 1

您可以将聚合与row_number():一起使用

select min(basis_adjutment) as basis_adjutment, min(participant_pct) as participant_pct
from (select t.*,
(case when basis_adjutment is not null
then row_number() over (case when basis_adjutment is not null then 1 else 0 end) order by basis_adjutment)
when participant_pct is not null
then row_number() over (case when participant_pct is not null then 1 else 0 end) order by participant_pct)
end) as seqnum
from t
) t
group by seqnum;

Answer 2

--
declare @t varchar(128) = 'countries', --tablename
@c_name varchar(300),
@sql varchar(max)
declare c cursor for
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @t;

set @sql = 'select * from '+@t+' where 1=1';
open c;
fetch next from c into @c_name;
set @sql = @sql + ' AND (' + @c_name + ' IS NOT NULL';
while @@FETCH_STATUS=0
begin
fetch next from c into @c_name;
set @sql = @sql + ' AND ' + @c_name + ' IS NOT NULL';
end;
close c;
set @sql = @sql + ')';
deallocate c;

EXEC (@sql)

----------
--you can also create procedure
CREATE PROCEDURE dbo.notnullcolumns
@t varchar(128)
AS
BEGIN

declare 
@c_name varchar(300),
@sql varchar(max)
declare c cursor for
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @t;

set @sql = 'select * from ' + @t + ' where 1=1';
open c;
fetch next from c into @c_name;
set @sql = @sql + ' AND (' + @c_name + ' IS NOT NULL';
while @@FETCH_STATUS=0
begin
fetch next from c into @c_name;
set @sql = @sql + ' AND ' + @c_name + ' IS NOT NULL';
end;
close c;
set @sql = @sql + ')';
deallocate c;

EXEC (@sql)

END