How to remove null values from each column ?

Sai Reddy

8y1.4k

Table as follows.

Expected result as follows.

20 | 10 | 40 | 30 |

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted answer
I can imagine that this probably is an interview question.

Try following:
select max([col1]) [col1]
  ,max([col2]) [col2]
  ,max([col3]) [col3]
  ,max([col4]) [col4]
from [dbo].[tableName]
Nitin Sontakke8y ago
In case of multiple rows, you obviously will need some grouping column. For example, in the values that you have given, assume that there is one more column called col0 and has values 1, 1, 1, 1, 2, 2, 2, 2 and 3 for 9 rows data you have posted.

Then following query can be used:
select [col0]
  ,max([col1]) [col1]
  ,max([col2]) [col2]
  ,max([col3]) [col3]
  ,max([col4]) [col4]
from [dbo].[tableName]
group by [col0]
Rafnas T P8y ago
I think it may not possible, you can do like this instead of null you can show 0 or any other values.to do like this, select isnull(col1,0), isnull(col2,0),isnull(col3,0) from tablename
Sai Reddy8y ago
Hi Rafnas T P,
The result should be as follows, it it possible?

20 | 10 | 40 | 30 |

30 | 20 | 50 | 40 |

11 | 36 | 80 | 90 |
Rafnas T P8y ago
hi sai,
did you get the proper solution, if not please explain what result you want exactly while you have multiple data, do you want sum of that row data
Nitin Sontakke8y ago
You will have to show the expected output in this case.

Needless to mention, the expected output should of course be logically consistent.
Sai Reddy8y ago
Thank you Nitin Sontakke, If column has multple values then how can we acheive?

Like as follws.
Sanghamitra Mohanty8y ago
Hi,

please reffer the Link:-
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/757a459c-ce39-4ab9-b688-78afa220b5ff/how-to-remove-those-columns-with-all-null-values?forum=transactsql

and anoother one simailar like that one :-
https://stackoverflow.com/questions/1665868/how-to-detect-and-remove-a-column-that-contains-only-null-values
Manav Pandya8y ago
Hello

You can make use of IsNull column to find the null values from all the columns

Please find below link for solution :

https://www.codeproject.com/Tips/493505/Using-one-Isnull-function-to-remove-all-null-value

Thanks