CODEWITHSUNDEEP
sqlsql-serverssms
Gohawks
Gohawks

Reputation: 1134

SQL Select Column if NOT Null

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?

Upvotes: 2

Views: 3300

Answers (2)

benjamin moskovits
benjamin moskovits

Reputation: 5468

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)

Upvotes: 1

CShannon
CShannon

Reputation: 135

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].

Upvotes: 1

Related Questions