Himadri Himadri - 1 year ago 123
SQL Question

How to detect and remove a column that contains only null values?

In my table table1 there are 6 columns Locations,a,b,c,d,e.

Locations [a] [b] [c] [d] [e]

[1] 10.00 Null Null 20.00 Null

[2] Null 30.00 Null Null Null

i need the result like

Locations [a] [b] [d]

[1] 10.00 Null 20.00

[2] Null 30.00 Null

My question is how to detect and delete column that contains all null values using sql query.
Is it possible?

If yes then please help and give sample.

Answer Source

How to detect whether a given column has only the NULL value:

SELECT 1  -- no GROUP BY therefore use a literal
  FROM Locations
       AND COUNT(*) > 0;

The resultset will either consist of zero rows (column a has a non-NULL value) or one row (column a has only the NULL value). FWIW this code is Standard SQL-92.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download