Fetch only not null columns from a table.

Sreekanth Reddy

7y1.2k

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.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Salman Beg7y ago
Accepted 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.
Packiaraj Santhiyagu7y ago
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 Gupta7y ago
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
Puneet Kankar7y ago
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