--case when else的一个用法：统计每行数据不为空的字段的个数：
--创建测试数据：
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
col1 varchar(5),
col2 varchar(5),
col3 varchar(5),
col4 varchar(5),
col5 varchar(5),
col6 varchar(5),
col7 varchar(5),
col8 varchar(5)
)
insert tbl
select '1',null,'2',null,'3','4','5','6' union all
select '1','2','3',null,null,null,null,'4' union all
select '1','2',null,'3','4','5','6','7' union all
select '1',null,null,'2','3',null,'4',null
--静态实现方法
--"+"想加统计各个“case when else”的结果来达到目的：
select *,
case when col1 is not null then 1 else 0 end+
case when col2 is not null then 1 else 0 end+
case when col3 is not null then 1 else 0 end+
case when col4 is not null then 1 else 0 end+
case when col5 is not null then 1 else 0 end+
case when col6 is not null then 1 else 0 end+
case when col7 is not null then 1 else 0 end+
case when col8 is null then 1 else 0 end as notnull
from tbl
--动态实现方法
declare @str varchar(max)
set @str=''
select @str= @str+'+ case when ' + name +' is not null then 1 else 0 end'+char(13)+char(10)
--char(13)+char(10)实现换行
from syscolumns where id = OBJECT_ID('tbl')
--print @str
set @str='select *,'+@str+' as notnull from tbl'
--print @str
exec(@str)
/*
j结果：
col1 col2 col3 col4 col5 col6 col7 col8 notnull
1 NULL 2 NULL 3 4 5 6 5
1 2 3 NULL NULL NULL NULL 4 3
1 2 NULL 3 4 5 6 7 6
1 NULL NULL 2 3 NULL 4 NULL 5
*/
