CODEWITHSUNDEEP
sqlsql-server
Steve
Steve

Reputation: 4681

SQL Filter Null Columns

I have a query like

select *
from tableName
where x='3'

This gives me back some number of results, but I don't want to see any columns where every row is Null. Is there an easy way to filter those out?

Upvotes: 1

Views: 2346

Answers (2)

Jon Mallow
Jon Mallow

Reputation: 406

You can try some crazy stuff with dynamics SQL but I really wouldn’t recommend this.

This query checks if first two columns have all null values and then adds them to select statement. If you really want to go this way you can to the same for all columns in your table.

DECLARE @sql nvarchar(1000)
DECLARE @columnList nvarchar(1000)
SET @columnList = ''

DECLARE @tableRowCount int
DECLARE @columnRowCount int

SET @tableRowCount = (select COUNT(*) from tableName)

SET @columnRowCount = (select COUNT(*) from tableName where column1 is null)
IF @tableRowCount <> @columnRowCount 
    SET @columnList = @columnList + 'column1, '

SET @columnRowCount = (select COUNT(*) from tableName where column2 is null)
IF @tableRowCount <> @columnRowCount 
    SET @columnList = @columnList + 'column2, '


IF LEN(@columnList) > 0
    SET @sql = 'SELECT ' + SUBSTRING(@columnList,1, LEN(@columnList) - 1) + ' FROM tableName'
ELSE
    SET @sql = 'SELECT * FROM tableName'

EXEC(@sql)

Upvotes: 2

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

No there isn't.

What you are after is some syntax like

select <column list::filter(where all rows are NULL)>

However, it just doesn't make sense. Not in a general sense. It might look cool when used in SSMS or a one-off query tool, but for day-to-day usage by Joe Public in programs like ASP.Net, who'd want an unpredictable number of columns?

Now if you really wanted to do it, this can be achieved with dynamic SQL, but it would have to be coded ONCE-PER-TABLE that you want to query this way.

Upvotes: 5

Related Questions