title: how to remove null values in a table sql
body: <p>I have a table (TestTable) as follows</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I would like a result as follows</p>
  
  <pre><code>PK  |  COL1  |  COL2  |  COL3  |  COL4  |  COL5
  1   |  1     |  43    |  1.5   |  7.8   |  1
  2   |  1     |  48    |  10.5  |  17.8  |  1
  </code></pre>
  
  <p>I have tried the following</p>
  
  <pre><code>Select 
  [COL1],
  [COL2],
  [COL3],
  [COL4],
   (select top 1 
  [COL5]  from  TestTable
  where [COL5] is not null and PK &lt;= 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 
  </code></pre>
  
  <p>The script works however I get empty result set. Any thoughts? </p>