How to remove null values from each column ?

Question

Table as follows. Expected result as follows. 20 | 10 | 40 | 30 |

Nitin Sontakke · 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]

Rafnas T P · Answer

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 Reddy · Answer

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 P · Answer

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 Sontakke · Answer

You will have to show the expected output in this case. Needless to mention, the expected output should of course be logically consistent.

Sai Reddy · Answer

Thank you Nitin Sontakke, If column has multple values then how can we acheive? Like as follws.

Sanghamitra Mohanty · Answer

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 Pandya · Answer

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

Nitin Sontakke · Answer

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]

How to remove null values from each column ?

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (9)