CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2null
abeuwe
abeuwe

Reputation: 169

how to remove null values in a table sql

I have a table (TestTable) as follows

PK  |  COL1  |  COL2  |  COL3  |  COL4  |  COL5
1   |  1     |  NULL  |  NULL  |  NULL  |  NULL
2   |  NULL  |  43    |  1.5   |  7.8   |  NULL
3   |  NULL  |  NULL  |  NULL  |  NULL  |  1
4   |  1     |  NULL  |  NULL  |  NULL  |  NULL
5   |  NULL  |  48    |  10.5  |  17.8  |  NULL
6   |  NULL  |  NULL  |  NULL  |  NULL  |  1

I would like a result as follows

PK  |  COL1  |  COL2  |  COL3  |  COL4  |  COL5
1   |  1     |  43    |  1.5   |  7.8   |  1
2   |  1     |  48    |  10.5  |  17.8  |  1

I have tried the following

Select 
[COL1],
[COL2],
[COL3],
[COL4],
 (select top 1 
[COL5]  from  TestTable
where [COL5] is not null and PK <= t1.pk
order by PK DESC) as [COL5]
FROM TestTable as t1 
Where 
[COL1] IS NOT NULL AND
[COL2] IS NOT NULL AND
[COL3] IS NOT NULL AND
[COL4] IS NOT NULL

The script works however I get empty result set. Any thoughts?

Upvotes: 0

Views: 3199

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

It looks like you have a value in col1 followed by a set of rows that should be combined into one row until the next value in col1.

If so, you can do this by assigning to each row a count -- the number of non-null values in col1 on or before the row. This can then be used for aggregation.

You don't mention the database, so I'll do this using a correlated subquery:

select row_number() over (order by max(pk)) as pk,
       max(col1) as col1,
       max(col2) as col2,
       max(col3) as col3,
       max(col4) as col4,
       max(col5) as col5
from (select t.*,
             (select count(t2.col1)
              from testtable t2
              where t2.pk <= t.pk
             ) as grpid
      from testtable t
     ) t
group by grpid;

Upvotes: 2

racraman
racraman

Reputation: 5034

You don't have any rows in TestTable where

[COL1] IS NOT NULL AND
[COL2] IS NOT NULL AND
[COL3] IS NOT NULL AND
[COL4] IS NOT NULL

Upvotes: 0

Related Questions