如果本列数据为空,如何去取上列不为空的值
如果本列数据为空,如何去取上列不为空的值
例:
表a
A B C D
2 3 NULL 4
1 NULL 3 2

得到结果
A B C D

2 3 3 4
1 1 3 2
[解决办法]

--动态的
create table test(A int,B int,C int,D int) 
insert into test
select 2,3,NULL,4
union all select 1,NULL,3,2
union all select null,5,null,20
go

declare @s varchar(1000)
select @s=''
select @s=@s+',['+a.name+']=case when ['+a.name+'] is null then ['+b.name+'] else ['+a.name+'] end'
from syscolumns a
left join syscolumns b on a.colid=b.colid+1 
where a.id=OBJECT_ID('test') and b.id=OBJECT_ID('test')
order by a.colid
exec ('select A'+@s+' from test')
drop table test

/*
ABCD
2334
1132
NULL5520  -->如果第一列为空，那只能取原值 了
*/

if object_id('tb') is not null
   drop table tb
go


create table tb(A int,B int,C int,D int) 

insert into tb
select 2,3,NULL,4
union all select 1,NULL,3,2
union all select null,5,null,20
go

declare @sql varchar(1000)
select @sql='' 

select @sql=@sql+',case when ['+d.name 
            +'] is null then '+c.name+' else ['+d.name+'] end as '+d.name
from sys.tables t
inner join sys.columns c
        on t.object_id = c.object_id
inner join sys.columns d 
       on d.column_id = c.column_id + 1
          and t.object_id = d.object_id
where t.name = 'tb'
order by c.column_id

set @sql ='select A'+ @sql +' from tb'


--输出动态语句
select @sql
/*
select A,
       case when [B] is null then A else [B] end as B,
       case when [C] is null then B else [C] end as C,
       case when [D] is null then C else [D] end as D 
from tb
*/


exec (@sql)
/*
A BCD
2 334
1 132
NULL 5520
*/