Himadri Himadri - 1 month ago 4
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

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;

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.

Comments