CODEWITHSUNDEEP
sqlfilter
user668660
user668660

Reputation:

How to filter empty columns from query in SQL?

I have a fairly wide table, which is sparsely populated with data. When I query it, I tend to get lots of VARCHAR columns that are empty.

A | B | C | D | E | F | G | H | I | J | K | L | 
  | x |   |   | x | x |   | x |   |   |   |   | 
  |   |   |   | x | x |   |   |   |   |   | x | 
  |   |   |   |   | x |   |   |   |   |   |   | 
  | x |   |   | x | x |   |   |   |   |   | x | 
  |   |   |   | x | x |   |   |   | x |   |   | 
  |   | x |   | x |   |   |   |   | x |   | x | 
  | x |   |   | x | x |   | x |   | x |   | x |

How can I filter out empty columns from the result set? I can't find a SQL keyword that seems to apply.

B | C | E | F | H | J | L | 
x |   | x | x | x |   |   | 
  |   | x | x |   |   | x | 
  |   |   | x |   |   |   | 
x |   | x | x |   |   | x | 
  |   | x | x |   | x |   | 
  | x | x |   |   | x | x | 
x |   | x | x | x | x | x |

Edit: This is for display purposes, and I wasn't planning to modify the table with data from the result set. I did consider that from an MVC perspective that it makes sense to leave the display of data to the view, but thought it not very efficient in terms of bandwidth. Perhaps that's not a worthwhile argument for doing it this way.

Upvotes: 4

Views: 3781

Answers (2)

Derek
Derek

Reputation: 23228

OK, so this is a somewhat complex set of dynamic-SQL generation that will give you what you're looking for. You'd have to stick this into a stored procedure if you want to use it.

FilterColTest is a test table I used for testing. I'll leave the definition, etc., in the query so that you can make the appropriate adjustments for your table/columns.

/*
create table FilterColTest (
    a int, b int, c int, d int, e int, f int, g int, h int, i int, j int)
insert into FilterColTest
select null,1,null,null,1,0,null,1,null,null
union select null,null,null,null,0,0,null,null,null,null
union select null,1,null,null,1,0,null,1,null,1
union select null,1,null,null,1,1,null,1,null,null
union select 1,1,0,null,1,0,null,1,null,null

--select * from FilterColTest
go
*/

declare @ColumnList table (ID int identity, colName varchar(max))

insert into @ColumnList(colName)
select column_name 
from information_schema.columns
where table_name = 'FilterColTest'

declare 
    @id int, @maxid int, @count int,
    @cols varchar(max), @sql nvarchar(max)

select @id = 1, @maxid = max(ID)
from @ColumnList

while @id <= @maxid
begin
    select @sql = 'select @count = count(*) from FilterColTest where ' + 
            colName + ' is not null'
    from @ColumnList
    where ID = @id 

    exec sp_executesql @sql, N'@count int output', @count output

    select @cols = isnull(@cols + ', ' + colName, colName)
    from @ColumnList
    where ID = @id and @count > 0

    set @id = @id + 1
end

select @sql = 'select ' + @cols + ' from FilterColTest'
exec sp_executesql @sql
go

/*
drop table FilterColTest
go
*/

Upvotes: 0

Blindy
Blindy

Reputation: 67362

That's a... really weird request. Are you using select *? The easiest way to fix that by far is to just say what columns you do want and you'll only get those back.

Why would you even want to randomly have disappearing columns depending on the range of values you try to select? What if your program/report/whatever is expecting a specific column to be present (even if null) and it gets silently removed because it is always null for the range?

Upvotes: 3

Related Questions