Fetch only not null columns from a table.

Question

How to fetch only not null columns from a table in sql server ? For Example : Let us think there are 3 columns x1, x2 and x3 with 10 records. If all the 10 record values of x3 column are null, Then I dont want to display that column. Only x1 and x2 needs to gets displayed. I dont want to go for looping technique.

Salman Beg · Answer

Hi. You can do like this, select sum(case when Column_1 is null then 1 else 0 end) as Column_1, sum(case when Column_2 is null then 1 else 0 end) as Column_2, sum(case when Column_3 is null then 1 else 0 end) as Column_3, from TestTable . If the result is 0, there are no Nulls. Thanks.

Puneet Kankar · Answer

Many ways to do this given below one of them declare countx3 int=0 set @countx3=Select count(x3) from table where x3 null If (@countx3>0) Select x1, x2, x3 from table Else Select x1, x2 from table

Packiaraj Santhiyagu · Answer

Hi, Try below one. DECLARE @query VARCHAR ( MAX ) -- @query is null at this point, so in the first ocurrence of coalesce the comma is ommited SELECT @query = COALESCE (@query + '+ ' , '' ) + 'case ISNULL(COUNT(' + c. name + '), 0) WHEN 0 THEN ' '' ' ELSE ' ',' + c. name + '' ' END' FROM sys.all_columns c INNER JOIN sys.all_objects o ON c.object_id = o.object_id WHERE o. name = 'test_20170407' AND c.is_nullable=1 set @query = ' declare @innerquery2 VARCHAR ( MAX ) select @innerquery2 = ' + @query + ' from test_20170407 set @innerquery2 = '' create view test_20170407_vw as select id '' + @innerquery2 + '' from test_20170407 GO '' print @innerquery2 --exec(@innerquery2) ' exec (@query) select * from test_20170407_vw

Amit Gupta · Answer

Have a look to the link https://www.sqlservercentral.com/Forums/1859594/how-to-select-only-non-null-columns-out-of-100-plus-columns-table