CODEWITHSUNDEEP
sqlsql-servernull
Abe
Abe

Reputation: 1929

How to move one row value to another row and remove all rows which are null SQL Server 2008

I have a table as below

PK   DBName API50 CounterValue
1    NULL    NULL   NULL
2    NULL    NULL   1
3    NULL    NULL   NULL
4    NULL    NULL   NULL
5    NULL    NULL   NULL
6    test1   34.5   NULL
7    NULL    NULL   NULL
8    NULL    NULL   NULL
9    NULL    NULL   NULL
10   NULL    NULL   2
11   test1   38.5   NULL

I would like the result table to be as follows

   DBName API50 CounterValue
    test1  34.5   1
    test1  38.5   2

As answered in the question below move one row value to another sql

Iam doing a self join but it results in CounterValue not being populated. Hope you guys can help me. Thanks in advance.

Upvotes: 0

Views: 74

Answers (4)

TechGirl
TechGirl

Reputation: 488

Try this query

select * from @table1
SELECT t1.ID, t1.DBName, t1.API50, t2.CounterValue
FROM @table1 t1 
INNER JOIN @table1 t2 ON t1.ID > t2.ID
WHERE t1.DBName IS NOT NULL
 AND t2.ID IN (select ID from @table1 where dbname is null and ID < t1.ID)
 AND t2.ID > (select ISNULL(MAX(ID),0) from @table1 where dbname is not null and ID < t1.ID)

Check this SQL FIDDLE

Upvotes: 0

Pankaj Kumar
Pankaj Kumar

Reputation: 685

Try this Query as well

**Select t.DBName,MAX(t.API50) as API50,tt.CounterValue from ( Select * from JustTest as t where DBName is not null) as t ,(Select* from JustTest as tt where CounterValue is not null )tt group by t.DBName,tt.CounterValue

Upvotes: 0

podiluska
podiluska

Reputation: 51494

If countervalue always increases in line with PK, 

select
    DBName, 
    API50, 
    (select max(CounterValue) from yourtable t1 where t1.pk <= yourtable.pk)
from yourtable
where DBName is not null

Upvotes: 2

valex
valex

Reputation: 24144

Try this query:

SELECT DBName,API50,
       (SELECT TOP 1
           CounterValue
           FROM t
           WHERE CounterValue is not null
           AND PK<=t1.PK
           ORDER BY PK DESC

        ) as CounterValue
FROM t as t1
WHERE DBNAme IS NOT NULL

SQLFiddle demo

Upvotes: 0

Related Questions