How Can I Select Only The Columns Which Is Not Null In Sql Server.?

You 'can' do this, but it's a VERY inefficient query. I would suggest looking at a db redesign or managing the return set for null columns.

Basically, you have to use pivot to turn columns into rows, select IS NOT NULL and then unpivot them back again.

Like I said, this is not at all efficient. The feature is only in T SQL by request, not design.

Not only is it not ideal for that reason, it's also a bugger to get your head around. Here is a link to help you get started:
https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[^]

I strongly suggest you don't use this technique and find a different solution to your problem as has been suggested in other solutions. By following this route you agree to not find me responsible for any migraines or brain aneurysm that may result

Good luck ^_^

declare @query varchar(max)
declare @querybit varchar(max)

select @querybit = (SELECT TOP 1(select SUBSTRING((select '+ ' + name from sys.all_columns
where object_id = OBJECT_ID('yourtablename')
and (system_type_id = 167)
for XML PATH('')),2,20000)) from yourtablename)

select @query = 'SELECT' + @querybit + ' FROM yourtablename'

exec (@query)

could do something a bit like this... you'll need to check sys.types for the correct system_type_id 's and thats hoping that your columns are all the same datatype...

and oops... read the question wrong I was looking at ways of filtering out rows that had null in some columns...

This can be done through dynamic SQL.
First we can write a query to find out the columns which are not null and then using dynamic sql fetch out the result.

Please see the sample code .



create table test1
(
s_no int not null,
name varchar(10) not null,
address varchar(10) null,
emailid varchar(100) null
)


insert into test1 (s_no, name)
values (1,'A'),(2,'B'),(3,'C')


declare @column_list varchar(8000),
@counter int

set @column_list = ''
set @counter = 0

while (Select max(colid) from syscolumns where id = object_id('test1') and isnullable= 0) > @counter
begin


select @counter = min(colid) from syscolumns where id = object_id('test1') and isnullable= 0 and colid > @counter
select @column_list = @column_list + ',' + (Select name from syscolumns where id = object_id('test1') and isnullable= 0 and colid = @counter)

end


select @column_list = SUBSTRING(@column_list,2,len(@column_list))

declare @sql varchar(8000)
select @sql = 'select ' + @column_list + ' from test1'
print @sql

exec (@sql)

Hope this help.

suppose you could concatenate the columns, a null value in any of them will make the computed column NULL

select * from test where id='10' and isnull(app1,'')<>'' or isnull(app2,'')<>'' or isnull(app3,'');''