SQL Select Column if NOT Null

Question

I have a Microsoft SQL Server table and let's say it has 10 columns. One or more of these columns may contain ALL null values. How would I construct a simple SELECT * query that excluded columns where ALL values are null?

What if I listed it out... i.e. select a,b,c, from tblx? Can I use the — Gohawks, Mar 31 '15 at 19:56
'case when' syntax to filter out the column or something similar to that? — Gohawks, Mar 31 '15 at 19:57
You would have to use dynamic SQL via that `exec` statement. A SQL query has a fixed set of columns, and you cannot just remove them. — Gordon Linoff, Mar 31 '15 at 19:58
You can probably get away with: `SELECT * FROM TABLENAME WHERE COLUMNAME NOT LIKE '%NULL%';` — ryekayo, Mar 31 '15 at 19:59
The whole idea of `SELECT * except some columns` [has already been discussed](http://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea). — Andriy M, Mar 31 '15 at 20:06
Why do you want to hide columns? If it's because you don't want to display them then do that in the display layer. SQL is designed to have defined result sets, not to display different column in different circumstances (hence why it's not trivial to do). — D Stanley, Mar 31 '15 at 21:33

score 1 · Answer 1 · answered Mar 31 '15 at 20:01

I do not believe there is a simple "SELECT * FROM [TABLE]" query that would exclude a column based on all of the values contained in the column in your result set. The select clause is defining what data to bring back, the from clause tells it ..well where from, and a where clause provides filtering criteria at a row-level.

The query you are talking about can almost certainly be written but it would not be a simple select * from [table].

score 1 · Answer 2 · answered Mar 31 '15 at 21:27

I did this for a table that has three columns (I assume at least one column does have data). You can extend this to as many columns as necessary:

  declare @strsql varchar(2500)
  set @strsql = 'select '
  set @strsql += 
  (select case when  (select COUNT(*) from #t1 where ean2 is null) <> (select count(*) from #t1) then 'ean2, ' else '' end)
    set @strsql += 
      (select case when  (select COUNT(*) from #t1 where ean1 is null) <> (select count(*) from #t1) then 'ean1, ' else '' end)
       set @strsql += 
      (select case when  (select COUNT(*) from #t1 where list_price is null) <> (select count(*) from #t1) then 'list_price, ' else '' end)
-- get rid of trailing ,
      set @strsql = LEFT(@strsql,len(@strsql) -1)
--add table to select from
      set @strsql += ' from #t1'
      exec (@strsql)

SQL Select Column if NOT Null

2 Answers2