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

​x
 
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

 
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

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

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

 
SELECT 1  -- no GROUP BY therefore use a literal  FROM LocationsHAVING COUNT(a) = 0        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.

Advertisement

Answer